FV of Monthly Deposits over Schedule of Varying Interest Rates

[Brholte]

Hello,

I want to compute FV of fixed monthly deposits over 30 years of historical rates of return

I am familiar with the FVSCHEDULE formula which will compute the future value of a lump sum over a schedule of varying interest rates

I am also familiar with the FV formula which will compute the future value of monthly deposits over a period of time with fixed interest rates

Is there a formula that will compute the future value of monthly deposits over a schedule of varying interest rates? I know how to build a table, but could greatly benefit from a formula that will compute this without all of that noise. I want to compute FV of monthly deposits over 30 years of historical rates

thanks!

 

[DRSteele]

I think there is no Excel function or closed-form formula that could contemplate the use of 360 interest rates over 360 periods of compounding and deposits. You'll just have to create a table of columns and rows.

 

[ask2tsp]

I wrote a UDF (user defined function). If you paste the code in a general module, you can use it in your worksheet.
Example: =FVperiodicDeposit(E3,C3:C26)

Option Explicit

Function FVperiodicDeposit(deposit As Single, periodicInterest As Range) As Single
    Dim interimValue    As Single
    Dim period          As Range
    
    interimValue = 0
    For Each period In periodicInterest
        interimValue = (interimValue + deposit) * (1 + period.Value)
    Next period
    FVperiodicDeposit = interimValue
End Function