Financial Formula With A Little Twist

[jhemmerly]

I am looking for a financial formula with a little twist. Let say I want to Deposit a sum of money ($500K) into an account that earns a guaranteed interest rate that veries(GIR increases every 5 years by .5% Starting with a 1.5%) This account will pay out a level amount for a stated number of years till the account = $0 (Lets say 21 years). Question... What is the level amount?


A typical formula with a fixed GIR would be:

PMT(rate,nper,pv,[fv],type)

PMT(?,21,$500,000,,1)

As you can see I am not sure how to add a rate that changes every 5 years. Can someone help me with this?

 

[Luke M]

I don't think there are any native functions within XL that can do what you're asking all in a single cell. Since it's a small number (21), perhaps you could just use 21 helper cells to calculate what the value of deposit would be after each year?


Here's my setup, with the word year being in cell A1:

[pre]

Year	value	        deduct	rate	1.50%
0	$500,000.00 	52050
1	$454,669.25 	52050
2	$428,789.50 	52050
3	$420,064.54 	52050
4	$428,736.94 	52050
5	$457,674.64 	52050
6	$411,709.01 	52050
7	$383,036.84 	52050
8	$369,050.33 	52050
9	$369,305.38 	52050
10	$385,465.29 	52050
11	$338,416.52 	52050
12	$304,980.34 	52050
13	$282,017.33 	52050
14	$267,911.94 	52050
15	$262,272.26 	52050
16	$213,375.59 	52050
17	$171,811.76 	52050
18	$133,534.36 	52050
19	$94,929.28 	52050
20	$52,098.32 	52050
21	$49.05 	        52050

[/pre]
B2, currently at 500,000 is an input cell. C2 is also an input cell.

B3 contains this formula:

=PMT($E$1+5%*(MOD(A3-1,5)),1,-B2+C2)

dragged down to B24 (year 21)

In C3:C23, I have this formula:

=$C$2

I then just manually played around with the input in C2, and quickly found that the level amount will be roughly $52,050

You might be able to have this value better found via Solver, depending on how much effort you want to put into it.

 

[jhemmerly]

Luke M. Thank you.

I am creating a calculator for my team of internal wholesalers. My hope is that I can just input the Lump Sum and the number of years we would like for the account to pay out and the calculator will do the rest. It will be a itemized report that can be saved as a PDF and emailed with a illustration.