# FV where interest rate is calculated daily but compounds monthly, and r can change daily

1. ## FV where interest rate is calculated daily but compounds monthly, and r can change daily

Hi all

I'm struggling to find a quick way to calculate the FV of a deposit where interest is calculated daily but compounded monthly, with the complication that the interest rate can change at any arbitrary day, and the deposit is not necessarily made on the first day of the month. I currently make use of amortization tables but need to streamline the process due to the volumes. I am attaching a simplified example of my problem. Is there a way to do this with formalae in Excel?

- hoping someone can point me in the right direction!

Thanks  Register To Reply

2. ## Re: FV where interest rate is calculated daily but compounds monthly, and r can change dai

Short but useless answer -- yes, there is a way to do this with formulae in Excel.

Obviously, the built in FV() and similar functions are built to assume evenly spaced payment periods and a constant interest rate. Something like the XNPV() function does not need evenly spaced periods, but it does assume a fixed interest rate.

I firmly believe that I must first understand the math/logic behind a problem before I can program the solution into Excel. So I put something like "future value with variable interest rate" into my favorite search engine and got nothing that helped me (a non-financial person) understand how one should calculate future value when the interest rate varies. I'm afraid I cannot help you at this point.

In order to help you help us get you a solution, I recommend a couple of things. One, make it clear whether this is specifically an Excel question (I know how to calculate this future value outside of Excel, but am having trouble moving the calculation into Excel) or a broader business math question (I don't know how anyone using any programming language or even paper, pencil, and hand calculator performs this calculation). If it is the latter, then you will need to wait until someone comes along who really knows business math who can explain this calculation to you. If it is the former, then help us non-business-math people understand the sequence of calculations that goes into this, and we'll help you put it into Excel.  Register To Reply