Help With Financial Formula

[mikeh]

Hi, I am looking for a formula that will help with financial planning.
Assume that I have R1,000,000 that I invest at 8% interest. The monthly Interest generated is used as income. At the end of the first year and every year after that I give myself a 6% increase to counter inflation. I would like to calculate what my income would be and how much capital would be left after X number of years? Any Ideas - Mike

 

[Skywalker42]

11 years

It will last 11 years of you keep taking 8% on 1 million instead
of the remaining capital. And the 6% increase on the salary of the previous year
--------------------------------------------------------
Capital Annually Monthly
--------------------------------------------------------
1 1000000.00 80000.00 6666.67
2 1000000.00 84800.00 7066.67
3 915200.00 89888.00 7490.67
4 825312.00 95281.28 7940.11
5 730030.72 100998.16 8416.51
6 629032.56 107058.05 8921.50
7 521974.52 113481.53 9456.79
8 408492.99 120290.42 10024.20
9 288202.57 127507.85 10625.65
10 160694.72 135158.32 11263.19
11 25536.40 143267.82 11938.98
12 -117731.41 xxxxxxxxxxxxxxxxxxxx
---------------------------------------h---------------------
Can't get columns to align.
Col 1 = year
Col 2 = Starting capital
Col 3 = Annual
Col 4 = Monthlyl

 

[broken1]

I assumed interest to compound monthly, and got 15 years:

	Balance	    Compound Interest     Withdrawal     Closing Balance
1	 1,000,000.00 	R -80,000.00	R -80,000.00	R 1,000,000.00
2	R 1,000,000.00	R -80,000.00	R -84,800.00	R 995,200.00
3	R 995,200.00	R -79,616.00	R -89,888.00	R 984,928.00
4	R 984,928.00	R -78,794.24	R -95,281.28	R 968,440.96
5	R 968,440.96	R -77,475.28	R -100,998.16	R 944,918.08
6	R 944,918.08	R -75,593.45	R -107,058.05	R 913,453.48
7	R 913,453.48	R -73,076.28	R -113,481.53	R 873,048.23
8	R 873,048.23	R -69,843.86	R -120,290.42	R 822,601.67
9	R 822,601.67	R -65,808.13	R -127,507.85	R 760,901.95
10	R 760,901.95	R -60,872.16	R -135,158.32	R 686,615.79
11	R 686,615.79	R -54,929.26	R -143,267.82	R 598,277.24
12	R 598,277.24	R -47,862.18	R -151,863.88	R 494,275.54
13	R 494,275.54	R -39,542.04	R -160,975.72	R 372,841.86
14	R 372,841.86	R -29,827.35	R -170,634.26	R 232,034.95
15	R 232,034.95	R -18,562.80	R -180,872.32	R 69,725.43

 

[mikeh]

Thanks Skywalker42, how did you get to that? Not sure what you mean by "keep taking 8% on 1 million instead
of the remaining capital" - Mike

 

[spiff]

is that assuming no bank fees?

 

[superskully]

Hi, I am looking for a formula that will help with financial planning.
Assume that I have R1,000,000 that I invest at 8% interest. The monthly Interest generated is used as income. At the end of the first year and every year after that I give myself a 6% increase to counter inflation. I would like to calculate what my income would be and how much capital would be left after X number of years? Any Ideas - Mike

How did u get 8% interest vs 6% inflation. Are you looking at high risk investments.
Normally, if you put in bank, the interest rate will counter inflation (dont forget about tax).

 

[Skywalker42]

Thanks Skywalker42, how did you get to that? Not sure what you mean by "keep taking 8% on 1 million instead
of the remaining capital" - Mike

Well, at the end of year 2 there will only be R 915200.00 because of the 6% increase in withdrawel during year 2.
In year 3, will you take 8% of R 915200 or still an amount equal to 8% of R 1000 000.

If you take 8% of R 915200 your income will actually decrease.
According to broken1's post my calculation may be incorrect anyway, he get 15 years.
I did the calculations on an anual basis instead of monthly.

Have a look at www.fincalc.com and click on
"How long will my money last?"

According to that calculator:
Your money will last approximately 14.5 years with systematic withdrawals totalling $1,836,260.

 

[Icarium]

That calculator seems pretty accurate. Broken1 was pretty close but didn't factor in that the capital amount would erode during the course of the year resulting in less interest earned and a faster overall erosion (ie: The capital on which interest is being earned would decrease from month 2 of year 2 so the interest in year 2 will actually be a little less than R80k, but not much less).

 

[mikeh]

Thanks to all, appreciate your input. fincalc looks like an excelent site. I Just wanted to try different scenarios based on different % figures. I have looked for an Excel function but cannot seem to find one that does the trick- regards Mike