Need help writing formula to calculate monthly returns based on annual growth rate

Everyone,

I have a model that makes projections based on annual growth rates. However, I need to evaluate the data based on monthly intervals. With an 8% return on an investment of $1,000,000 my ending balance at the end of the year should be $1,080,000 and in year 2 it would be $1,166,400 and so on. In order to evaluate the monthly data I need each month in year 1 to be based off $1,000,000, so it would be 8%/12=.006666% or $6,666.66 per month. The next year would be based off $1,000,000 + (6,666.66*12) = $1,080,000 and each month would be $7,200.

I need help writing a formula to evaluate over 360 periods. Workbook attached. Thanks!

This doesn't tie with my annual growth rate

Anyone have any other ideas for this formula?

Originally Posted by bxk006

I have a model that makes projections based on annual growth rates. However, I need to evaluate the data based on monthly intervals. With an 8% return on an investment of $1,000,000 my ending balance at the end of the year should be $1,080,000 and in year 2 it would be $1,166,400 and so on. In order to evaluate the monthly data I need each month in year 1 to be based off $1,000,000, so it would be 8%/12=.006666% or $6,666.66 per month. The next year would be based off $1,000,000 + (6,666.66*12) = $1,080,000 and each month would be $7,200.

You are mixing assumptions. On the one hand, you assume exponential year-to-year growth at 8% per year. On the other hand, you assume linear month-to-month growth of $xxxxx per month.

Usually, we assume exponential month-to-month growth as well. The monthly rate is (1+8%)^(1/12)-1 or RATE(12,0,-1,1+8%) in D4. Then put the following formula into D9 and copy down: =D8*(1+$D$4).

If you insist on linear month-to-month growth, put the following formula into D9 and copy down: =D8+IF(MOD(C9-1,12)=0,D8*$C$4/12,D8-D7).

In either case, the formula in D8 is simply: =C5*(1+C4/12).

Thanks, I needed to turn the yearly growth rate into an monthly percentage.