## When Cash Flows Don’t Match Compounding Periods

In a previous post Simple Formula for Converting Compound Interest Rates and spreadsheet, I showed a conversion formula that allowed the user to convert an interest rate from one compounding period to another equivalent compounding period. For example, you could calculate the annual equivalent for a monthly compound rate, or the semiannual equivalent rate of a quarterly compound rate, etc.

This same conversion formula can be used for another purpose. Assume that you have an investment vehicle that compounds interest monthly at 4.0%, but you only plan on making a deposit at the end of each quarter. Even though the investment compounds monthly, you are only investing quarterly , so when calculating the amount you will accumulate at the end of three years, do you plug in the monthly compound rate or the quarterly compound rate into the future value formula? The answer is that you use the quarterly equivalent of the monthly 4.0% compound rate, or 4.01335% (don’t round off).

The formula for the conversion is:

 =IF(t>500,f*LN(1+g/f),IF(f>=500,t*(EXP(g/t)-1),(t*((1+g/f)^(f/t))-t)))

The values f and t come from this lookup table, corresponding to the monthly and quarterly rates, respectively:

Now you can plug in the information to the FV or PV functions. The new APR and number of deposits per year are linked to the rate converter, so you only need to enter the amount of each deposit and the number of years:

On a separate sheet (Proof), I calculated the present and future values the long way, as a proof that the above shortcut formula works: