Forum Discussion
Issue with FV formula using a non-integer Term
I am having an issue when using the FV function for a 'term' that is not an integer. So, the formula works fine when compounding, let's say, a $10,000 investment (with no additional deposits) for 5 years at 5%. The formula is =-FV(0.05,5,0,10000), and the correct result is $12,762.82. But if the term is 5.5 years, the formula of =-FV(0.05,5.5,0,10000) gives a result of $13,077.99. A manual calculation of this will give the correct result of $13,081.89.
I believe that the issue with the FV formula is that using the underlying mathematical formula of =10000*(1+.05)^5.5 compounds the interest amount by raising the interest rate to a non-integer. The proper underlying formula should be =10000+(((10000*(1+0.05)^INT(5.5))-10000))+(5.5-INT(5.5))*0.05*((10000*(1+0.05)^INT(5.5))).
Would like to hear what others have to say about it.
Thank you.
2 Replies
Norman_Shapiro_1958 I'm by no means an expert on the subject but when I translate 5.5 years to 66 months and use a monthly interest rate of 0.4074123783648% (which translates to 5% per annum), the FV function returns 13,077.99. So, I believe Excel correctly translates the 5% to accurate monthly rates when you use fractions of years.
When I go the simple way of calculating 10,000 x (1.05)^5 x 1.025, thus 5 years at 5% plus 2.5% for the extra half year, the result is 13,081.89. The same as the formula you proposed. I don't believe this is the accurate answer though, as it approximates the half year interest to be 2.5% where it should be 2.4695%.
- Don't agree.
1. The interest is in fact, in my case, being compunded on a yearly basis. The formula is set up to accept a yearly interest rate. It must, therefore, either require an integer as the term OR calculate a partial term separately. Requiring the user to seprately calculate the underlying interest that would equate to the yearly interest rate when compounded does not fit with the Formula as it is being used.
2. The actual rate for the 1/2 year is 2.5% and for 3 months it would be 1.125% - as there is no "intra-term" compunding.
3. The simple formula that you propose is much more elegant (and works properly). So I will go with that. Thank you!
