Working backwards to find initial starting value

moony

New Member
Joined
Jul 22, 2019
Messages
10
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.



Thanks for your help!

-Moony
 

Some videos you may like

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

sheetspread

Well-known Member
Joined
Sep 19, 2005
Messages
5,117
This?

<b>Excel 2010</b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th><th>I</th><th>J</th><th>K</th><th>L</th><th>M</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style=";">Year</td><td style="text-align: right;;">1</td><td style="text-align: right;;">1</td><td style="text-align: right;;">1</td><td style="text-align: right;;">1</td><td style="text-align: right;;">1</td><td style="text-align: right;;">1</td><td style="text-align: right;;">1</td><td style="text-align: right;;">1</td><td style="text-align: right;;">1</td><td style="text-align: right;;">1</td><td style="text-align: right;;">1</td><td style="text-align: right;;">1</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style=";">Month</td><td style="text-align: right;;">1</td><td style="text-align: right;;">2</td><td style="text-align: right;;">3</td><td style="text-align: right;;">4</td><td style="text-align: right;;">5</td><td style="text-align: right;;">6</td><td style="text-align: right;;">7</td><td style="text-align: right;;">8</td><td style="text-align: right;;">9</td><td style="text-align: right;;">10</td><td style="text-align: right;;">11</td><td style="text-align: right;;">12</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style=";">Month Ending</td><td style="text-align: right;;">8/31/2019</td><td style="text-align: right;;">9/30/2019</td><td style="text-align: right;;">10/31/2019</td><td style="text-align: right;;">11/30/2019</td><td style="text-align: right;;">12/31/2019</td><td style="text-align: right;;">1/31/2020</td><td style="text-align: right;;">2/29/2020</td><td style="text-align: right;;">3/31/2020</td><td style="text-align: right;;">4/30/2020</td><td style="text-align: right;;">5/31/2020</td><td style="text-align: right;;">6/30/2020</td><td style="text-align: right;;">7/31/2020</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style="text-align: right;;">15000</td><td style="text-align: right;;">$1,222 </td><td style="text-align: right;;">$1,227 </td><td style="text-align: right;;">$1,232 </td><td style="text-align: right;;">$1,237 </td><td style="text-align: right;;">$1,242 </td><td style="text-align: right;;">$1,247 </td><td style="text-align: right;;">$1,252 </td><td style="text-align: right;;">$1,258 </td><td style="text-align: right;;">$1,263 </td><td style="text-align: right;;">$1,268 </td><td style="text-align: right;;">$1,273 </td><td style="text-align: right;;">$1,278 </td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style="text-align: right;;">30000</td><td style="text-align: right;;">2443</td><td style="text-align: right;;">2454</td><td style="text-align: right;;">2464</td><td style="text-align: right;;">2474</td><td style="text-align: right;;">2484</td><td style="text-align: right;;">2495</td><td style="text-align: right;;">2505</td><td style="text-align: right;;">2515</td><td style="text-align: right;;">2525</td><td style="text-align: right;;">2536</td><td style="text-align: right;;">2546</td><td style="text-align: right;;">2557</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style="text-align: right;;">0.004166667</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:4.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet4</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">B4</th><td style="text-align:left">=-PMT(<font color="Blue">$A$7,12,0,15000</font>)*(<font color="Blue">(<font color="Red">1+$A$7</font>)^(<font color="Red">COLUMN(<font color="Green">A1</font>)-1</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">A5</th><td style="text-align:left">=A4*2</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">B5</th><td style="text-align:left">=B4*2</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">A7</th><td style="text-align:left">=0.05/12</td></tr></tbody></table></td></tr></table><br />
 

moony

New Member
Joined
Jul 22, 2019
Messages
10
Thank you!!! It didn't cross my mind to use PMT formula as i wasn't calculating costs!

I really appreciate your help!

-Moony
 

Watch MrExcel Video

Forum statistics

Threads
1,102,508
Messages
5,487,298
Members
407,590
Latest member
Grobler

This Week's Hot Topics

Top