Creating a payback calculator with rate change [Solved/Closed]
jojosabadar5
Posts Monday July 18, 2016Registration date
July 22, 2016
Last seen

Jul 18, 2016 at 11:42 AM  Latest reply: jojosabadar5
Posts Monday July 18, 2016Registration date
July 22, 2016
Last seen
 Jul 22, 2016 at 02:14 PM
Hey, having a bit of trouble, appreciate any help.
I am trying to create a payback calculator where you input:
Initial Investment
Rebates
Annual Electrical Savings
Rate 1
Years Rate 1
Rate 2
And what is displayed is a table with:
Column A = year
Column B= that years Saving
Column C = display total savings (total savings  initial investment, start out negative with initial investment greater then savings until eventually positive)
The way i was hoping it would work is:
I would input the initial investment, any rebates possible, the annual electrical savings, the initial rate, how long that rate is in, the new rate. (if there needs to be a limit for the seconds rate it could be a total of 20 or 25 years for rate 1 and 2.)
I would get back:
A table with the rows having years (120or 25), savings for that year and then the total savings (yr1 displays (yr1 savingsinitial investment), yr2 displays (yr2 savings+yr1savings)initial investment, etc.)
Sorry, my apologies, I should have made that part more clear.
I was hoping to get a table with 2025 rows of data as the output.
The first column is just the year values (start year 0, which would be the initial investment and any rebates)
The second column is the savings for that year:
as an example : If I set rate1 as 0.05 $/kWh for 5 years, and rate2 as 0.15$/kWh after that. then the first 5 rows (years 15) would have the second column as Rate1*Annual electrical savings, then from years 6 onward this coloumn would be Rate2*AnnualElectricalSavings. What would be displayed would be the same for the first 5 years for this example before changing.
The last column would be the total savings  initial investment; Where the total savings would be the second column + the previous years final column.
As i mentioned above, i was hoping it would be possible to input the year for rate 1 every time as it is not always going to be 5 years.
I hope this explains what i was looking for a little better, if not just let me know and i can try to make it more clear.
Thanks a lot for taking the time to help!
What i am having trouble with is having to manually enter the rate for every row.
So from my example above, if it was 5 years, then i have to manually enter rate1 for the first 6 rows, years 05, (or enter it once and drag it down) and then enter rate 2 from that row onward.
Is there a repeat function that i can manually enter the year limit for rate 1 and it populates the table with rate 1 for the first however many rows and then populate the rest with rate 2 until the end of the table?
Thanks again for your help!
I created an IF loop with (years <= inputted years, rate 1, rate 2) and used the drag function to apply to the full table.
Thanks for everyone who took the time to try and help.
Could a mod let me know if I should close this, or mark it solved?
So from my example above, if it was 5 years, then i have to manually enter rate1 for the first 6 rows, years 05, (or enter it once and drag it down) and then enter rate 2 from that row onward.
Is there a repeat function that i can manually enter the year limit for rate 1 and it populates the table with rate 1 for the first however many rows and then populate the rest with rate 2 until the end of the table?
Thanks again for your help!
Thanks for everyone who took the time to try and help.
Could a mod let me know if I should close this, or mark it solved?
Thanks!