pedro-egoli
Well-known Member
- Joined
- Apr 25, 2004
- Messages
- 1,217
- Office Version
- 2016
- Platform
- Windows
Thanks to some help earlier, on another thread, formula in column Z of my spreadsheet produces the number of years it takes for capital outlay to be repaid by savings from that expenditure (payback time).
The spreadsheet relates to an exercise on which solar energy system to purchase.
In designing spreadsheet I wanted to see what the actual return (ROI) was on the capital invested up to the time capital was returned.
After the capital was returned I then wanted to see what the savings was each year on my power bill.
In the copy of part of spreadsheet below you will note that column AD covers the first part of my exercise (ROI) and AE the second (saving on power bill).
The figures shown in column AE were manually moved from column AD by me to differentiate the two.
Spreadsheet looks like this
What I would like is a formula that would transfer the figures in column AD to AE after the number of years is shown in column Z
Thanks
Pedro
The spreadsheet relates to an exercise on which solar energy system to purchase.
In designing spreadsheet I wanted to see what the actual return (ROI) was on the capital invested up to the time capital was returned.
After the capital was returned I then wanted to see what the savings was each year on my power bill.
In the copy of part of spreadsheet below you will note that column AD covers the first part of my exercise (ROI) and AE the second (saving on power bill).
The figures shown in column AE were manually moved from column AD by me to differentiate the two.
Spreadsheet looks like this
Excel Workbook | ||||||||||
---|---|---|---|---|---|---|---|---|---|---|
X | Y | Z | AA | AB | AC | AD | AE | |||
10 | Net | * | * | Total Power Cost without Solar | Net Power Cost with Solar | Saving | * | * | ||
11 | * | * | * | * | * | * | * | * | ||
12 | * | * | * | * | * | * | * | * | ||
13 | * | * | * | * | * | * | * | * | ||
14 | * | * | no years | * | * | * | Net ROI | %SAVED | ||
15 | $ * * *10,627.13 | 0 | * | 1394.76 | -$ * *1,085.01 | 2,479.77 | 20.05% | * | ||
16 | $ * * * *8,768.04 | 1 | * | 1499.36 | -$ * * * 997.36 | 2,496.72 | 23.49% | * | ||
17 | $ * * * *6,778.49 | 2 | * | 1611.82 | -$ * * * 903.82 | 2,515.63 | 28.69% | * | ||
18 | $ * * * *4,648.55 | 3 | * | 1732.70 | -$ * * * 803.95 | 2,536.65 | 37.42% | * | ||
19 | $ * * * *2,367.56 | 4 | * | 1862.65 | -$ * * * 697.25 | 2,559.91 | 55.07% | * | ||
20 | -$ * * * * * *75.94 | 5 | 4.97 | 2002.35 | -$ * * * 583.20 | 2,585.55 | 129.13% | * | ||
21 | -$ * * * *2,689.70 | 6 | * | 2152.53 | -$ * * * 461.22 | 2,613.75 | * | 121.43% | ||
22 | -$ * * * *5,334.37 | 7 | * | 2313.97 | -$ * * * 330.71 | 2,644.68 | * | 114.29% | ||
24Kwh et panels16at190) |
What I would like is a formula that would transfer the figures in column AD to AE after the number of years is shown in column Z
Thanks
Pedro