Thread: Working backwards to find initial starting value Thanks:  1 Post #5313978 (1) Likes:  1 Post #5313978 (1)

1. Working backwards to find initial starting value

Hello forum,

I have a question that I'm unable to correctly search for a solution since I'm unable to word it correctly for a search. Initially I had been looking at some kind of reverse CAGR solution, but what I found wasn't related to my problem.

What I'm trying to do is take a Year End total sum value (say 15,000 summed over 12 months), provide an annual growth rate that's divided upon the 12 months, and find my Starting Value.
If this was only for me I would just use Solver/Goal Seek, but my boss (who doesn't know or want to know how to use these types of functions in Excel) would like to input a Year End total summed value and have it provide all 12 values growing at the determined rate, all summing up to the number he wants to total up to.

This is what I mean visually. I was able to do this using Solver, but I was hoping there was a mathematical way to do it using formulas.

-Moony

2. Re: Working backwards to find initial starting value

This?

Excel 2010
ABCDEFGHIJKLM
1Year111111111111
2Month123456789101112
3Month Ending8/31/20199/30/201910/31/201911/30/201912/31/20191/31/20202/29/20203/31/20204/30/20205/31/20206/30/20207/31/2020
415000\$1,222 \$1,227 \$1,232 \$1,237 \$1,242 \$1,247 \$1,252 \$1,258 \$1,263 \$1,268 \$1,273 \$1,278
530000244324542464247424842495250525152525253625462557
6
70.004166667

Sheet4

Worksheet Formulas
CellFormula
B4=-PMT(\$A\$7,12,0,15000)*((1+\$A\$7)^(COLUMN(A1)-1))
A5=A4*2
B5=B4*2
A7=0.05/12

3. Re: Working backwards to find initial starting value

Thank you!!! It didn't cross my mind to use PMT formula as i wasn't calculating costs!

-Moony