Calculating return on investment

[jonwhite1972]

Good Morning all,
I am working on an Energy calculator and as part of the customer facing end I want to show a graph that shows return on investment. For this graph i need to show the current energy cost along with the savings the client will achieve using our products. The problem i currently have is i wish to automate the calculations and show the saving over 10 years how ever i also need to allow for the cost of install. I have in column A2 to A13 the years 1 to 12, in B1 I have the cost of installation and in B2 to B13 I have the amount saved per annum. So if the cost of install is £3000 (B1) and the saving is £2000 per annum I need a sum in C2 that reflects £0 in year 1, £1000 in year 2 and £2000 in Year three to year ten. Both the cost of installation and the saving per annum changes depending on the data inputted by the client so the sum needs to equate for this.
Any help will be very much appreciated.

 

[oldbrewer]

	install	3000
	savings per yr	2000
	years	1	2	3	4	5	6	7	8	9	10
	savings	-1000	1000	3000	5000	7000	9000	11000	13000	15000	17000
	return on investment % (cum. per year)	-33.3	16.7	33.3	41.7	46.7	50.0	52.4	54.2	55.6	56.7
			savings in year 1 are minus 1000 pounds
			thereafter the cumulative savings are as shown
			with the running ret on inv

<colgroup><col><col><col span="13"></colgroup><tbody>
</tbody>

 

[FormR]

Hi, welcome to the forum

Here is a formula you can try in C2 copied down.

Excel 2013/2016
	A	B	C
1		3000
2	Year 1	2000	0
3	Year 2	2000	1000
4	Year 3	2000	2000
5	Year 4	2000	2000
6	Year 5	2000	2000
7	Year 6	2000	2000
8	Year 7	2000	2000
9	Year 8	2000	2000
10	Year 9	2000	2000
11	Year 10	2000	2000
Sheet1

Cell Formulas
Range	Formula
C2	=MIN(B2,MAX(0,SUM(B$2:B2)-$B$1))

 

[jonwhite1972]

Thanks that's a great help

 

[jonwhite1972]

Hi FormR. That worked perfectly, Thank yo so much. Would you also mind telling me what the function is doing?

 

[FormR]

Would you also mind telling me what the function is doing?

Hi, there's not too much going on there but maybe it would help to see it broken down.

Excel 2013/2016
	A	B	C	D	E	F
1		3000	Step 1	Step 2	Step 3	Step 4
2	Year 1	2000	2000	-1000	0	0
3	Year 2	2000	4000	1000	1000	1000
4	Year 3	2000	6000	3000	3000	2000
5	Year 4	2000	8000	5000	5000	2000
6	Year 5	2000	10000	7000	7000	2000
7	Year 6	2000	12000	9000	9000	2000
8	Year 7	2000	14000	11000	11000	2000
9	Year 8	2000	16000	13000	13000	2000
10	Year 9	2000	18000	15000	15000	2000
11	Year 10	2000	20000	17000	17000	2000
Sheet1

Cell Formulas
Range	Formula
C2	=SUM(B$2:B2)
D2	=C2-$B$1
E2	=MAX(D2,0)
F2	=MIN(E2,B2)

 

[jonwhite1972]

Got it!! I would never have thought to use Min/Max. Thanks again.. Jon