I work for a Ukrainian insurance company that invests excess funds in fixed income products.
They like to do everything in Excel.
I have gotten pretty good at calculating compound monthly interest on all their deposits, i.e.
=PreviousMonthValue*(1+InterestRate/12)
However, some deposits are compounded quarterly, not monthly. Knowing how to search, I looked up the quarterly compounding formula, which is: =PreviousValue*(1+InterestRate/4)
This works and correctly calculates the quarterly interest. For example, on the amount of 100,000 UAH deposited on Jan 1, 2012, the total amount with quarterly compounding comes to 103,000 UAH on April 1, 2012.
Now the dilema: The balance is done every month... so even though it's quarterly, they want to keep a running total every month, i.e. 101000 on Feb 1, 102000 on Mar 1 and then 103000 on Apr 1.
I guess I could take 103000, subtract the previous value (100000) and then average out the interest (3000) over 3 months manually every time, but I would have to keep changing the formula. Is there a way to take advantage of the power of Excel and write a consistent formula that would average it out correctly, say for a period of 36 months? Please help! Sorry so long! Thanks!
Bookmarks