Message Font: Serif | Sans-Serif
 
UnThreaded | Threaded | Whole Thread (10) | Ignore Thread Prev | Next
Author: warrl Big funky green star, 20000 posts Old School Fool Add to my Favorite Fools Ignore this person (you won't see their posts anymore) Number: of 5076  
Subject: Re: Calculating interest Date: 2/24/2004 11:51 AM
Post New | Post Reply | Reply Later | Create Poll Report this Post | Recommend it!
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.
Post New | Post Reply | Reply Later | Create Poll Report this Post | Recommend it!
Print the post  
UnThreaded | Threaded | Whole Thread (10) | Ignore Thread Prev | Next

Announcements

Pencils of Promise - Back to School Drive
"Pencils of Promise works with communities across the globe to build schools and create programs that provide education opportunities for children."
Post of the Day:
Value Hounds

Considerable Headwinds for Profire Energy?
What was Your Dumbest Investment?
Share it with us -- and learn from others' stories of flubs.
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.
Community Home
Speak Your Mind, Start Your Blog, Rate Your Stocks

Community Team Fools - who are those TMF's?
Contact Us
Contact Customer Service and other Fool departments here.
Work for Fools?
Winner of the Washingtonian great places to work, and "#1 Media Company to Work For" (BusinessInsider 2011)! 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.
Advertisement