Message Font: Serif | Sans-Serif

No. of Recommendations: 0
I'm working on a retirement planning spreadsheet under the theory that when completed, I'll understand the whole process a whole lot better than if I use a pre-made retirement calculator.

A simple question: when calculating the effects of interest for this purpose (starting with an assumed yearly growth rate), is it best to simply use that yearly growth rate and intervals of years, or divide the growth rate by 12 and use intervals of months? The latter appears to yield a higher return, which upon reflection makes sense if you're applying the interest each month. I suppose it doesn't make a whole lot of difference as long as consistency is maintained - I'm applying an arbitrary guesstimate as to future growth rates, after all.

For purposes of retirement planning, what is the standard way of doing this? When you say "I expect my investments will grow 8% a year", what exactly are you saying - you end the year with \$X + \$X*.08, or (\$X + \$X*.0067) compounded 12 times?

FWIW, I'm using the Excel Future Value formula:
FV(Rate,# payment periods,payments per period,Beginning value)

Thanks for helping!
No. of Recommendations: 0
I meant to add, given that monthly contributions are to be factored into my spreadsheet, it seems that working in months would be preferable. That said, I've never seen a retirement calculator ask what your expected monthly return on your investments might be...

How does one convert an estimated annual growth rate into it's monthly components?

- fiduciarily challenged, but willing to learn...
No. of Recommendations: 3
A simple question: when calculating the effects of interest for this purpose (starting with an assumed yearly growth rate), is it best to simply use that yearly growth rate and intervals of years, or divide the growth rate by 12 and use intervals of months?

To be precisely correct you must use the same method as is used in the official this-is-what-somebody-pays interest calculation.

But in practice it usually doesn't matter all that much. Sure, 12% interest per year, versus 1% interest per month, is over half a percent a year difference... and if there's enough time for that to amount to anything worthwhile, there's enough time that the rate of return could fluctuate.
No. of Recommendations: 1
I meant to add, given that monthly contributions are to be factored into my spreadsheet, it seems that working in months would be preferable. That said, I've never seen a retirement calculator ask what your expected monthly return on your investments might be...

It IS critical (or, at the very least, vastly simpler) that the same period size be used throughout any one calculation.

So if you're adding more cash to your retirement account monthly, you should be using a monthly rate of return.

Usually you'll be close enough to just divide the annual percentage by 12.

If you are quite certain that the annual figure is a percentage YIELD, rather than an interest rate (i.e. it includes the effects of compounding within the year), you can go the formal route. Which is:

Given the annual yield as a decimal fraction, e.g. 0.12 for 12% per year:

Monthly rate = (annual yield + 1) ^ (1/12) - 1
No. of Recommendations: 0
Given the annual yield as a decimal fraction, e.g. 0.12 for 12% per year:
Monthly rate = (annual yield + 1) ^ (1/12) - 1

------
Does the typical retirement calculator ask for your expected annual Yield or Interest? It appears they don't (or shouldn't, rather) use the two interchangably, but perhaps I'm wrong.

I don't quite feel comfortable just dividing the annual interest rate by 12, as a couple of tests show quite a difference over a substantial length of time. Granted, these are all estimations and there may well be other, greater, variations - but I'd prefer to limit those to the elements I cannot control for.

so...
A) Do standard retirement calculators ask for expected annual interest rates and simply use that as a Yield rate for purposes of compounding (and taking account of monthly contributions) under the assumption that the error generated is not meaningful?
or
B) Do they ask for annual interest rates and convert that to annual Yield prior to doing their compounding calculations?
or
C) Something else entirely

I bumped up against another problem while working on this last night:
D) Given an annual rate of growth of your investment funds, the annual rate of disbursement of these funds, and the number of years they will be disbursed, how does one then calculate (in Excel) the remaining funds after the final disbursement?

I can do the math on paper and in Excel in stepwise fashion, but that's quite klugy. I tried using the Future Value formula, but with no joy, either. Is there another defined formula, am I missing how to use FV, or can someone help me out with a different formula that can be implemented in Excel that can take into account X periods, both in disbursals and interest accrued?

Thanks again for the help!

- I told you I was fiduciarily challenged..
No. of Recommendations: 3
I don't quite feel comfortable just dividing the annual interest rate by 12, as a couple of tests show quite a difference over a substantial length of time.

If the interest actually compounds monthly, which is typical of money-market funds and market-rate bank accounts, then dividing the annual interest rate by 12 is the correct procedure.

However, unless you are going to buy individual fixed-rate bonds and hold them to maturity, the difference between different ways of computing interest probably doesn't create as much uncertainty over a "substantial length of time" as is provided by the likelihood of future fluctuations in interest and inflation rates.

When I'm looking forward a substantial (or even a moderate) length of time, I treat my results as estimates.

A) Do standard retirement calculators ask for expected annual interest rates and simply use that as a Yield rate for purposes of compounding (and taking account of monthly contributions) under the assumption that the error generated is not meaningful?
or
B) Do they ask for annual interest rates and convert that to annual Yield prior to doing their compounding calculations?
or
C) Something else entirely

Every financial calculator I have seen, even if it ASKS for annual rates, works just fine if you do everything on a monthly basis. I haven't seen one yet that I could confirm would treat the rate entered as anything other than a PERIODIC rate, compounding once per period.

I can do the math on paper and in Excel in stepwise fashion, but that's quite klugy.

In my opinion, a klugy method that you understand and can examine for errors is better than an elegant method that you don't understand.

I tried using the Future Value formula, but with no joy, either. Is there another defined formula, am I missing how to use FV, or can someone help me out with a different formula that can be implemented in Excel that can take into account X periods, both in disbursals and interest accrued?

Excel's financial formulas have a bug which would be very confusing to the "fiduciarily challenged". The bug creates a bit of extra work for those who are accustomed to dealing with this stuff.

There really is only one formula in financial calculations - everything else is a rearrangement, simplification, repetition, or some combination of those, of the one formula. That formula is:

PV * (1+rate)^periods = PMT * (1 + rate*type) * ((1+rate)^periods-1)/rate - FV = 0

(Check the help on any of Excel's financial functions for what the type is.)

Microsoft's bug: see that minus sign right before FV, near the end of the formula? They have a plus sign. (Fortunately, they are CONSISTENT in that bug.)

So (taking the FV function as an example) if you enter the present value as YOUR EQUITY in the account (positive for assets, negative for debts), and the payment amount as positive, then the result of Microsoft's function is how big a payment you would have to make to get to zero. If it's an asset, the hope is that this payment will be a large negative number.

If they didn't have that bug, the result of the formula would be the future value of the account.

Of course, the future value of the account and the size of the payment you would have to make to get the account to zero are the same - except that one is positive and the other is negative. Like I said, confusing.

One way to deal with the bug is to create a SIMPLE problem that you can easily examine yourself and understand the results. For example, the future value of an account with a present value of \$1 (an asset), an interest rate of 10% per period, and a periodic deposit of \$10, for one period. You can do that in your head: the proper result is \$11.10. A result of -\$11.10 is acceptable and understood. Anything else is wrong. Once you get the signs right so you get an acceptable result, you can then change the inputs (but not their signs, except deliberately changing to a debt) to reflect your actual problem, and trust that you know the proper result.
No. of Recommendations: 1
For purposes of retirement planning, what is the standard way of doing this? When you say "I expect my investments will grow 8% a year", what exactly are you saying - you end the year with \$X + \$X*.08, or (\$X + \$X*.0067) compounded 12 times?

Keep in mind that however you choose to do it, your actual results will differ because you won't have a nice smooth, steady move up.

For instance, if you assume 8% flat annual growth over 5 years, you end up with a return of 46.9% (1.08 ^ 5). But if you have 4 years that return 15% and 1 year where you lose 20%, even though the arithmetic average of the returns is 8% (((15% x 4) + (-20% x 1)) / 5), the actual total return is only 39.9% (1.15 ^ 4 x 0.80).

The point I'm trying to make is not that you have to build a really complicated calculator, but rather just that you have to be careful about any assumptions you make to simplify a model.

good luck,
dan
No. of Recommendations: 1
The point I'm trying to make is not that you have to build a really complicated calculator, but rather just that you have to be careful about any assumptions you make to simplify a model.
------
Point taken.

My purpose isn't so much to estimate my retirement income within \$1,000/yr, but primarily to work through all the aspects of the calculations so I understand what is going on and have a better idea of how all the elements interact. To that end, I'd rather err on the side of a little more complexity for accuracy, even if that accuracy is not strictly speaking, necessary - but again, more towards the goal of understanding the process than extremely accurate estimations.

Thank you both for your help; it's appreciated!
No. of Recommendations: 0
```One way to deal with the bug is to create
a SIMPLE problem that you can easily examine
yourself and understand the results. For example,
the future value of an account with a present
value of \$1 (an asset), an interest rate of 10%
per period, and a periodic deposit of \$10, for
one period. You can do that in your head: the
proper result is \$11.10. A result of -\$11.10
is acceptable and understood. Anything else is
wrong. Once you get the signs right so you get
an acceptable result, you can then change the
inputs (but not their signs, except deliberately
changing to a debt) to reflect your actual problem,
and trust that you know the proper result.

I like the explaination. I try to think more concrete.
If I put a dollar in savings (etc), it's cash
outflow- therefore it's negative. So, when I call
up future values, they will be positive.

Example:

n     i     PV     PMT    FV
12    10     0     -1      ?

FV =  21.38

Investing a dollar a month for 12 months (-1 PMT = cash
out) will equal 21.38 cash inflow after 12 months.

Just my .02

Stetson20

```
No. of Recommendations: 0
I think it makes sense to use monthly compounding when you're making monthly contributions. Otherwise the FV will be either overestimated (if you're using type 1 with annual compounding) or underestimated (if you're using the formula, or type 0). In that situation monthly compounding gives a more moderate estimate.

The way I use FV for monthly compounding is Rate/12, Nper*12, -Pmt, -Pv, Type 1. Note that Pmt is the amount of your monthly, not annual, contribution. Pmt and Pv are negative, for the reason warrl pointed out.

Excel can handle D) from above via the PMT function, using the Fv that you calculated earlier as the Pv. This will return the amount you can spend each year in retirement. PMT's Fv should be left blank for the spend-down calculation. You can also use PMT to calculate the minimum contributions to reach a desired balance, by leaving Pv blank and using your goal amount in Fv. In either case, because of Excel's bug, use negatives for Fv and Pv.

Hope this helps.