Understanding the Time Value of Money in Excel

The time value of money concept is the idea that money today is worth more than the same amount of money in the future. This is because money today can be invested and earn interest, which increases its value over time. Money in the future, on the other hand, has less purchasing power due to inflation, which reduces its value over time.

One way to understand the time value of money concept is to imagine that you have two options: receive $100 today or receive $100 in one year. Which option would you choose? Most people would choose to receive $100 today, because they can either spend it now or invest it and earn more money by next year. If you invest $100 today at a 10% interest rate, you will have $110 in one year. Therefore, $100 today is worth more than $100 in one year.

Another way to understand the time value of money concept is to think about how much you would be willing to pay today for a certain amount of money in the future. For example, how much would you pay today for $100 in one year? You would probably pay less than $100, because you are giving up the opportunity to use or invest that money today. The difference between the present value and the future value of money is the time value of money. The time value of money depends on the interest rate, the number of compounding periods, and the length of time.

Basic Theory

At its core, the time value of money is based on the idea that a certain amount of money today has a different value than the same amount in the future. This is because money can earn interest over time, making a present sum more valuable than an equal sum received or paid in the future.

The basic formula for calculating the future value (FV) of a present sum (PV) is:

Where:

• is the present value of the money.
• is the interest rate per period.
• is the number of periods.

Procedures in Excel

1. Entering the Data:
   • Open Microsoft Excel and create a new worksheet.
   • Label three columns: “Present Value (PV),” “Interest Rate (r),” and “Number of Periods (n).”
   • Input the relevant values into each column.
2. Applying the Formula:
   • In a new column, label it “Future Value (FV).”
   • Use the formula =PV * (1 + r)^n to calculate the future value.
3. Formatting Cells:
   • Format cells to display currency or percentage as needed.
   • To round the result, use the ROUND function, e.g., =ROUND(PV * (1 + r)^n, 2).

Explanation and Scenario

Consider a scenario where you invest $5,000 at an annual interest rate of 5% for 3 years. The future value can be
calculated using the time value of money formula.

Applying the formula, the Future Value (FV) is calculated as follows:

Therefore, the future value of the $5,000 investment after 3 years at a 5% annual interest rate is approximately
$5,788.13.

Other Approaches

1. Using Excel Functions:
   • Excel offers dedicated functions for time value of money calculations, such as FV (Future
     Value), PV (Present Value), and RATE (Interest Rate).
2. Data Tables:
   • Excel’s Data Table feature allows you to perform multiple what-if analyses by varying one or more input
     values.
3. Goal Seek:
   • The Goal Seek feature in Excel can be used to find the required input (e.g., interest rate or number of
     periods) to achieve a specific future value.