Previous Topic Next Topic

Creating a payback calculator with rate change

Solved/Closed
jojosabadar Posts 5 Registration date Monday 18 July 2016 Status Member Last seen 22 July 2016 - 18 Jul 2016 à 11:42
jojosabadar Posts 5 Registration date Monday 18 July 2016 Status Member Last seen 22 July 2016 - 22 Jul 2016 à 14:14
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 (1-20or 25), savings for that year and then the total savings (yr1 displays (yr1 savings-initial investment), yr2 displays (yr2 savings+yr1savings)-initial investment, etc.)

Any help is greatly appreciated.
Thanks
Related:

1 response

Réponse 1 / 1
Blocked Profile
18 Jul 2016 à 17:57
Do you know the formulas that calculate all of those values?
jojosabadar Posts 5 Registration date Monday 18 July 2016 Status Member Last seen 22 July 2016
19 Jul 2016 à 09:26
Sorry, my apologies, I should have made that part more clear.

I was hoping to get a table with 20-25 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 1-5) 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!
Blocked Profile
19 Jul 2016 à 16:52
It sounds like you have a handle on it....what in particular are you stuck on?
jojosabadar Posts 5 Registration date Monday 18 July 2016 Status Member Last seen 22 July 2016
19 Jul 2016 à 19:37
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 0-5, (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!
jojosabadar Posts 5 Registration date Monday 18 July 2016 Status Member Last seen 22 July 2016
21 Jul 2016 à 10:51
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?

Thanks!
Blocked Profile
21 Jul 2016 à 15:44
If you are satisfied, you may mark it as solved!

Similar discussions

How to calculate positions (1st,2nd,3rd...) in Excel
hardy -
TrowaD -

6 responses