Message Font: Serif | Sans-Serif
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?
B) Do they ask for annual interest rates and convert that to annual Yield prior to doing their compounding calculations?
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.
Print the post  


When Life Gives You Lemons
We all have had hardships and made poor decisions. The important thing is how we respond and grow. Read the story of a Fool who started from nothing, and looks to gain everything.
Contact Us
Contact Customer Service and other Fool departments here.
Work for Fools?
Winner of the Washingtonian great places to work, and Glassdoor #1 Company to Work For 2015! Have access to all of TMF's online and email products for FREE, and be paid for your contributions to TMF! Click the link and start your Fool career.