Calculations Table

Posted by Mike Tiley on November 15, 2001 1:47 AM

Hi,

I want to make a simple table that will calculate investment value, so if I enter how much I want to save, over how many years, and also enter a fixed interest rate, it will tell me how much money I need to save each month. I'm sure this is quite simple, but I'm having trouble working out the calculations.

Any help would be much appreciated

Thanks, Mike

Posted by Joe Was on November 15, 2001 11:32 AM

You will need cells to hold your data:

Nunber of years payments will be made. {Years}

Number of payments per year. {Frequency}

Yearly interest. {i}

Future value.(Amount you want to total to.) {FV}

Payment at begining of period [0] or end of period [1]. {Type}

Payment(The amount you need to deposit each payment.) {PMT}

In the formula below you can reference the data cell locations or named ranges.

=PMT(i, n, PV, FV, Type)
where n = (Years x frequency)or[Years*frequency]

That should give you what you want!
Note: PV should be negative and your result [PMT] will be as well, all other values will be positive for the proper indicated cash flow. JSW

Posted by Joe Was on November 15, 2001 11:41 AM

Note2: "i" will need to be adjusted for the "frequency" of payments or the compounding period. So, if you do this for 10 years with semiannual payments in an investment with 6% interest compunded semiannually, then: n=10*2=20 and i=6/2=3. JSW

Posted by Joe Was on November 15, 2001 11:47 AM

The correct "Type" is 0=End and 1=Begining of period, sorry I got this backwards in the other notes. JSW : Payment(The amount you need to deposit each payment.) {PMT} : In the formula below you can reference the data cell locations or named ranges. : =PMT(i, n, PV, FV, Type)

Posted by Mike Tiley on November 16, 2001 9:01 AM

Thanks Joe, but i cant seem to get it right,

I have entered the no. of months, the interest rate, the present value as 0 and the future value as 15000, but it gives the result as -£42.42, which dosnt seem to add up, any ideas on what im doing wrong?

Thanks for your advice

mike