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:
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
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.
Subscribe
