In this article we looked at the effect of extra payments into ones home loan (mortgage bond). That’s all good and well, but what if you don’t have a loan? Perhaps you would rather invest in a Unit Trust or Interest Bearing bank account and would now like to calculate the future value of your investment to help you plan better.
Microsoft Excel has a simple formula called FV which will help you to calculate the future value of your investment. This does not account for changing interest rates or complicated scenarios, but it will give you a simple tool to give you an idea of how your money can grow.
Let’s get started
To start with a simple example, let’s save R600 each month for 10 years (120 months) at an interest of 6%. The answer as you see is R98,327.61.
That’s pretty easy. Let’s quickly look at the input parameters:
Rate: This is the interest rate per period. Generally speaking, financial institutions will quote an annual interest rate. In this example it is 6%. To get the monthly interest rate we simply use 6%/12. If we change the example an decide to make quarterly payments we would need to use 6%/4.
Nper: This is the number of periods (in total) for which we will make a payment. If we wish to pay monthly, this is the number of months.
Pmt: This is the amount we wish to pay each period. This figure cannot change over the lifetime of the investment. But, see further down how we can circumvent this potential problem. Excel expects the payment to be a negative figure as payments out of your account would “minus” from your account. You will see that if you use a positive figure, your answer will show negative. The figure will be the same though. (If that is confusing, try it yourself)
Pv: The present value. If you are adding money to an existing bank account, use Pv as the present value in the account. If this to calculate a new investment leave it blank or type a zero in the box.
Type: This is to specify whether the payment is being made at the beginning of each period, or at the end. This will affect the interest that you earn. By default Excel will assume you are paying at the end of each period.
So, in the example above we assumed a constant R600 payment each month for 10 years. But what if you decide to pay 10% more each year? For that we will need to spice things up a bit. See how I have calculated the Future Value for 12 months at a time. I then use the answer as my Present Value for the following year. The monthly payment is simply increased by 10% in each row.
Excel is full of some really useful financial formulas and here’s a quick tutorial on how to calculate the loan payment and see what a difference a lower interest rate will make.