Originally Posted by
janagan
year Jan Feb March April Total Interest
2001 450 434 2323 2323 ...............???
What is the formula I can use to get a total interest for this monthly installment on saving account. [....] Interest Rate 3%
That assumes the values 450, 434, 2323 etc are in B2:M2. It also assume that payments are at the beginning of the month, and we want interest earned through the end of Dec.
The FV expression calculates the total principal and interest through the end of Dec. The SUM expression is the total principal.
If Excel had an NFV function (net future value), the total principal could be calculated simply by NFV(3%/12,B2:M2). Since it does not, we calculate the NPV, then appreciate (grow) it to the future date.
We compound growth by 13 periods instead of 12 because Excel NPV discounts back to the month before Jan. That is, the first term in the Excel NPV sum is B2/(1+r); so we must multiply by (1+r)^13 to calculate B2*(1+r)^12, the future value of B2.
Bookmarks