Working backwards to find initial starting value

moony

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

w2HvfwI.jpg


Thanks for your help!

-Moony
 

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.
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
Cell Formulas
RangeFormula
B4=-PMT($A$7,12,0,15000)*((1+$A$7)^(COLUMN(A1)-1))
B5=B4*2
A5=A4*2
A7=0.05/12
 
Upvote 0
Thank you!!! It didn't cross my mind to use PMT formula as i wasn't calculating costs!

I really appreciate your help!

-Moony
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,256
Members
448,557
Latest member
richa mishra

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top