...
Your Ad Here

Wednesday, September 21, 2005

Excel - Future Value (FV)

I was waiting for mINg's Fundalmental III before I post this post but I guess he is too busy to write at the moment. I have nothing much to share on money and investment today but some tricks that a lot people doesn't know. I learnt this when I accidentally read a book in Popular JB. Before this I was using Excel formula to calculate interest rates.

Before you try this, you should know at least about Excel's basic function. FV stands for future value for an investment based on an interest rate and constant payment.

Open MS Excel and click on Fx (or "=" for older version) at the left hand side of the Excel edit.


FV( interest_rate, number_payments, payment, PV, Type )

interest_rate = the interest rate for the investment for a year.
number_payments = the number of payments for a year.
payment = the amount of the payment made each payment.
PV (optional) = the present value of the payments.
Type (optional) = mode the payments are due,
0: Payment due on beginning of month (normally for loan)
1: Payment due on end of month (normally for saving because you only save on end of the month).

Now, for the example:

Let say you save $500 per month, the interest rate is 2.5% per year. How much can you save in 3 years? You have zero balance in the bank when you start off your saving.

First key in values such as below and choose the result cell and click on the function button. A window will popup and you can key in the cell coordinates or use cell selector to select cells.


Interest rate is divided by 12 because it is based on monthly saving. And of course the duration is 3 years x 12. Add negative to monthly saving as you are adding money into the account (if you don't understand, the sign is always opposite between present value and payment).

So let's start a large calculation. We go back to student loan which we discussed two weeks ago. Let say the loan is $40,000 with installment of $500 - %1,000 per month. How will be the progress in few years time. (Actually there is better way to calculate the duration you need to clear the loan but I will use this example for the meantime to analyse your loan in few years time.)


The red value means your loan is payoff (it is negative value in fact). In order not to type formula for quite number of time I suggest you use Fill Handler to fill/copy formula. It is how did during my work as an Engineer (we cut off the timeline).

If you don't know Fill Handler in Excel, I will roughly do some demonstration here but practise makes perfect. If you select a cell in Excel, please notice there is a small dot at the bottom right hand corner of the cell. If you put your mouse pointer on it, the pointer will change to a black cross.

When you type formula, use "$" sign to tell Excel the column/row is a constant. Let say the spreadsheet with no coordinate above is starting from A and 1. For instance, the interest rate is a constant (C2). Type the interest rate as $C$2. For installment of $500 (C3), we want $500 to be constant but not the year (C21:C30). So we type $C$3 for installment and $C21 for year. After this, when we drag the black cross (fill handler) down from C21 to C30. It will automatic change and fill the formula for you in each cell from C21 to C30. Just remember, without "$" Excel will "fill/change" for you.

In short, $Cnn = constant column, inconstant row, C$nn = inconstant column, constant row, $C$nn = constant column, constant row.

If my explanation is unclear please comment below and I will explain again. (I know it is unclear). Remember to use Excel to try out (practise makes perfect).

In the next issue, I will try to write more about Excel in Finance. (It is important for you to know if you wish to play with figures in your investment or wealth management plan).

No comments:

Related Posts Plugin for WordPress, Blogger...