Mr. Roads is well-set with his financial plan, even with inflation
higher than forecast.
There are 3 questions here:
1. does he have enough to afford $2,000 per month with inflation at
4% -- assuming that he doesn?t touch savings or his house but can use
the core portfolio?
2. what spending level would he have if the core $180,000 is
untouched (at 4% inflation)?
3. how much could he spend each year if the portfolio goes to zero in
at his 90th birthday?
These are all forms of Net Present Value calculations, including the
annuity that he would enjoy from the portfolio (#3).
Now here?s the key data:
? Social Security is indexed and will provide a real annual return of
$9,000, no matter what the inflation rate will be.
? Mr. Roads real concern is what happens to income & expenses with the
other $1,250 per month ($15,000 per year).
? He has a portfolio (9%) and a savings account (5%) to produce the
other $15,000 per year.
So let?s proceed to answer the questions:
1. Year 1 he has portfolio and savings to try to get the needed $1,250
which is not provided by Social Security:
Income = $16,200 + $600 = $16,800
Added savings: $1,800
In year 2, savings increase but expenses increase faster:
Income = $16,200 + (.05) * $13,800 = $16,890
Expenses = $15,00- * 1.04 = $15,600
Added savings are down = $1,290
It?s easier to iterative work like this with a spreadsheet:
?Alfred Roads Annual Financials?
Note that savings build through year 4. Also, that we stay above
$12,000 balance there through year 7 ? then the portfolio withdrawals
have to start.
In the end, he still has $30,000 of his portfolio and all of the
savings ? but inflation?s going to pretty well eliminate the portfolio
in year 21.
2. Here?s a scenario where we need to know what he can spend without
touching the $180,000 in principal and without touching the $12,000 in
savings. In this case, we have to reduce the spending not covered by
Social Security ($15,000) enough that interest from growing savings in
the early years can keep an Annual Investment Net that doesn?t drop
savings below $12,000.
You can keep changing the number in cell B26 until you get there ? the
number is $12,019 ? PLUS his inflation-indexed Social Security of
$9,000 = $21,019.
That in contrast to $24,000 per year above but says that reducing
monthly spending from $2,000 per month to about $1,752 will leave the
principal in his portfolio, his savings and his house investment in
place should Roadsie live beyond 90.
3. Scenario #3 sets up an annuity for his investment portfolio and
adds the Social Security and savings:
In Excel this is calculated with PMT function, the same function that
would be used to calculate a loan. In this case, it?s an annuity,
with money coming back each month to Mr. Roads:
Rate: is the interest rate for the loan ? and this has to match the
term (if monthly, then 0.75% or if yearly, then 9%)
Nper is the total number of payments for the loan, here 20 years
Pv is the present value or principal -- $180,000.
Fv is the future value, or a cash balance you want to attain after
the last payment is made. If fv is omitted, it is assumed to be 0
(zero), that is, the future value of a loan is 0.
Type is the number 0 (zero) or 1 and indicates when payments are
due. We?ll make sure that he has the money at the beginning of each
year, a 1.
The $180,000 paid as an annuity would be $18,090. With Social
Security and cash from savings he?d have about $27,690 annually. Note
that he could actually spend a bit more because his $18,090 tucked in
a savings account would earn another $452.25 in savings ? you can add
that or ignore it (I'd add it).
Adding it brings annual spending to $28,142.
Since this is your first experience with Google Answers, please be
sure to use the Clarification Request if any portion of this Answer is