Working backwards to find initial starting value

[moony]

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

 

[sheetspread]

This?

Excel 2010
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Year	1	1	1	1	1	1	1	1	1	1	1	1
2	Month	1	2	3	4	5	6	7	8	9	10	11	12
3	Month Ending	8/31/2019	9/30/2019	10/31/2019	11/30/2019	12/31/2019	1/31/2020	2/29/2020	3/31/2020	4/30/2020	5/31/2020	6/30/2020	7/31/2020
4	15000	$1,222	$1,227	$1,232	$1,237	$1,242	$1,247	$1,252	$1,258	$1,263	$1,268	$1,273	$1,278
5	30000	2443	2454	2464	2474	2484	2495	2505	2515	2525	2536	2546	2557
6
7	0.004166667
Sheet4

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

 

[moony]

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

I really appreciate your help!

-Moony