The Time Value of Money
Sadly, just about everything these days requires money. In a time where the media are using as many boot-quaking adjectives as they can to describe the state of the economy, money matters can be a real headache. Thankfully, Excel can help to ease that headache, if only slightly.
A couple of handy formulas in Excel that will be useful to just about anyone are related to the time value of money. In this blog post we will touch briefly on the formula ‘future value’ (=FV).
FV shows the amount that will be returned from an investment that carries a constant interest rate over a set number of periods, which means it is perfect for finding out potential returns from a term deposit or a savings account (although savings accounts are, more often than not, subject to fluctuating interest rates).
Let’s use an example to test the formula:
You want to grow your savings and are doing some research into which savings account will give you the best return over a year. You look in the paper and find that a local bank is currently offering a savings account with an interest rate of 5.5% p.a. At present you have $1000 on hand, and are able to save an additional $150 per month.
So, first of all, let’s sort that data out into the variables of the formula:
[Will include a screenshot here]
Notice how the cell next to FV is left blank? That is because the future value, in this case the level your savings account will be at after a year, is the variable that we are trying to determine.
From there it is as simple as entering that data into the formula, like so:
1. Click on the formula selection box next to the formula bar.
2. In the box below ‘search for a formula’, type in ‘FV’. [Will include a screenshot here]
3. Click Go.
Excel will prompt you as to which data needs to be entered in each box, which is as simple as clicking on the figure next to the appropriate variable. Ignore the TYPE variable for now, as it is not needed in this example. Click OK. The formula should return a figure of -2902.48.
From the above example there are a few things to note. First of all, did you notice how PV was entered as a positive value, while FV returned a negative value? This is because the formula works by considering the future value of an investment from the perspective of one party to the investment. In this example, the formula is taken from the perspective of the bank. The original $1000 is received by the bank at the beginning of the investment period (an inflow, shown as a positive figure), and $2902.48 is paid out by the bank at the end of the investment (an outflow, shown as a negative figure). Thus, it would be impossible for PV and FV to both return a positive or negative value.
Secondly, did you notice how the figure next to RATE is not 0.05(5%)? This is because the interest rate must be measured on the same basis as NPER and the PMT, or in other words, over the same period of time. In the example, $150 was saved each month over the period of a year. As there are 12 months in a year, the number of periods in this case is 12. Since the interest rate of 5% is per year, it must be divided by the number of months to determine what portion of the interest rate is allocated to each month. If the interest rate were not divided by 12, the formula would assume that the interest rate is 5% per month, as opposed to per year, which will return a much higher result.
So there you have it. Though the future value formula may not seem terribly exciting, it can prove useful in getting the most out of your investments. And hey, a few extra dollars wouldn’t hurt!