Retirement Calculation Formula Help

B

Barry

Start 1,000.00 (This number changes)

Interest Rate 5.00 (This number changes)

Monthly Draw 16.30 ( Formula needed here to
adjust 12/60
to 0.00)


55 56 57 58 59 60


1 1,000.00 851.02 694.41 529.79 356.75 174.86
2 987.87 838.26 681.00 515.70 341.94 159.29
3 975.68 825.46 667.54 501.55 327.06 143.65
4 963.45 812.59 654.02 487.34 312.13 127.95
5 951.16 799.68 640.45 473.07 297.13 112.18
6 938.83 786.71 626.82 458.74 282.07 96.35
7 926.44 773.69 613.13 444.35 266.94 80.45
8 914.00 760.61 599.38 429.90 251.75 64.49
9 901.51 747.48 585.58 415.39 236.50 48.46
10 888.96 734.30 571.72 400.83 221.19 32.36
11 876.37 721.06 557.80 386.20 205.81 16.19
12 863.72 707.76 543.83 371.50 190.37 -0.04


This might be a circular reference as you adjust the monthly draw it
changes all the other numbers as well. I need the last (bottom rt.)
number to be 0.00 and the monthly draw number to automatically change.

Any Ideas ??? Thanks,

Barry
 
C

Cliff

If it doesn't absolutely need to be automatic, use Tools / Goal Seek to get your answer

Set the last set to zero by adjusting the variable
 
R

Ron Rosenfeld

Start 1,000.00 (This number changes)

Interest Rate 5.00 (This number changes)

Monthly Draw 16.30 ( Formula needed here to
adjust 12/60
to 0.00)


55 56 57 58 59 60


1 1,000.00 851.02 694.41 529.79 356.75 174.86
2 987.87 838.26 681.00 515.70 341.94 159.29
3 975.68 825.46 667.54 501.55 327.06 143.65
4 963.45 812.59 654.02 487.34 312.13 127.95
5 951.16 799.68 640.45 473.07 297.13 112.18
6 938.83 786.71 626.82 458.74 282.07 96.35
7 926.44 773.69 613.13 444.35 266.94 80.45
8 914.00 760.61 599.38 429.90 251.75 64.49
9 901.51 747.48 585.58 415.39 236.50 48.46
10 888.96 734.30 571.72 400.83 221.19 32.36
11 876.37 721.06 557.80 386.20 205.81 16.19
12 863.72 707.76 543.83 371.50 190.37 -0.04


This might be a circular reference as you adjust the monthly draw it
changes all the other numbers as well. I need the last (bottom rt.)
number to be 0.00 and the monthly draw number to automatically change.

Any Ideas ??? Thanks,
Click to expand...

The formula for Draw: =PMT(Rate/12,71,Start,0,0)

or, if you want to show exactly what you'll be paying (the formula above
actually returns ($16.299520674854800) but that might be tough to write out a
check for :)) :

=ROUND(PMT(Rate/12,71,Start,0,0),2)

(will be a negative number)

In your amortization table:

55/1 is Cell B6

B6: =Start
B7: =B6*(1+Rate/12)+Draw

copy/drag down to B12

Then select B7:B17 and copy/drag across to Column G

C6: =B17*(1+Rate/12)+Draw

Select C6 and copy/drag across to G6



--ron
 
Top