Calculate value of investment with variable interest + periodic payments (Excel/Google Sheets)

Question

I know about FV and FVSCHEDULE but how do I combine them?

Say I make monthly payments into an account with a variable interest rate ... how do I calculate future value? Or do I have to build out the table 'manually'?

Answer 1

FV calculates the future value of a periodic payment given a single rate, and FVSCHEDULE gives you the future value of a single payment at a varied interest rate. There's not a built-in function to calculate the FV of multiple payments at multiplerates.

You could calculate the FV of each payment by providing the future interest rate schedule (also monthly) and then add them together.

For example, for payment 1 you'd call FVSCHEDULE passing in the payment amount and the interest rates for periods 1,2,..N. For payment 2 you'd use the interest rates for periods 2,3,...N and so on.

But, it might be simpler just to build out the schedule yourself, adding each payment to the balance and adding in the resulting interest for each period.