Report

Creating a payback calculator with rate change [Solved]

Ask a question jojosabadar 5Posts Monday July 18, 2016Registration date July 22, 2016 Last seen - Last answered on Jul 22, 2016 02:14PM
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
See more 
Helpful
+0
moins plus
Do you know the formulas that calculate all of those values?
ac3mark 6225Posts Monday June 3, 2013Registration date ModeratorStatus December 9, 2016 Last seen - Jul 19, 2016 04:52PM
It sounds like you have a handle on it....what in particular are you stuck on?
Reply
jojosabadar 5Posts Monday July 18, 2016Registration date July 22, 2016 Last seen - Jul 19, 2016 07:37PM
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!
Reply
jojosabadar 5Posts Monday July 18, 2016Registration date July 22, 2016 Last seen - Jul 21, 2016 10:51AM
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!
Reply
ac3mark 6225Posts Monday June 3, 2013Registration date ModeratorStatus December 9, 2016 Last seen - Jul 21, 2016 03:44PM
If you are satisfied, you may mark it as solved!
Reply
jojosabadar 5Posts Monday July 18, 2016Registration date July 22, 2016 Last seen - Jul 22, 2016 02:14PM
Will do.
Reply
Add comment

Member requests are more likely to be responded to.

Members can monitor the statuses of their requests from their account pages.

A CCM membership gives you access to additional options.

Not a member yet?

Sign up now. It takes less than a minute and is completely free!