Excel Conundrum - Formula for break even year

captain
captain used Ask the Experts™
on
Hi

I have the following scenario:

I have a large Excel spreadsheet that is part of a quotation system to our customers and this illustrates the financial outlay for a piece of equipment and the payback period that equipment gives to the customer. The customer receives an annual estimated payment which is index linked (rises with inflation) and after x years the system has paid for itself, with future years generating profits.

So far we have only illustrated to our customers a worst case scenario where all payments are flat without being index linked, as we cannot predict the future and did not want to over promise. But sales have had a few conversations now when customers have seen quotes from other companies with much shorter payback periods, nearly losing the deal as the competitors showing this with inflation rises included.

So we now want to show both. For example:

Equipment cost (C) is £15,000 and the annual payment (p) is £1,500 the break even is C/p or 10 years.

Now with inflation (i) the payment is p+(p*i)=p1; p1+(p1*i)=p2 etc.

I am displaying this in a table showing a 20 year projection for payments and a graph to illustrate this from a starting point of -£15,000.

My issue is that the payments are ever increasing up to a point in time when the total payment is equal to the capital cost. As this is a curve simple calculations for C divided by p don't work...and I need to show:

Break even based on 0% inflation = x.x years
Break even based on 4% inflation = y.y years


So as I need to show the break-even year on a capital investment where payments towards this are index linked, how do I get the exact figure when there is no simple method to calculate it?

thanks much
capt.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Commented:
Did you tryed "What-If" Excel function?

http://www.excel-user.com/2012/02/using-what-if.html

jppinto
captainTechnical Director

Author

Commented:
Is this also available in Excel 2007?
captainTechnical Director

Author

Commented:
Having just read this I cannot see the benefit of the What-If here. To clarify, I already have the table and all the payments over the years, I was not looking for how to populate my payments per year in a table!

I have a fixed capital cost of x.

I now have annual incrementing payments over 20 years

Somewhere between the ?th and the ?th year the payments will be accumulatively greater than the capital cost x.

When exactly is that?
I used p = p0*power((1+i),y-y0) to compute the payment p in year y, given annual inflation i starting with payment p0 at the end of year y0.

Accumulated payments P(y) are easily computed by addition to the previous cell.

I then used an if(and()) to isolate the year range in which the break-even point occurs.

In that year range, I used linear interpolation
Y(be) = Y(i) + (Y(i+1)-Y(i)*(C-P(i))/(P(i+1)-P(i))

Open in new window

to arrive at a fractional year of break-even. Outside that year range, I set to "" (blank). Then I used max() on that row to pick out the fractional year of break-even.

You could convert the fractional year to a year-month, or ceiling up to get the year in which accelerated break-even occurs.

There may be a more elegant way to isolate the year range within which to do the interpolation, but this is easier for the average Excel user to understand.
Indexed-Break-Even-Example.xlsx
captainTechnical Director

Author

Commented:
Perfect, that is exactly what I was looking for.

I don't need the exact month calculated, but have to specify after how many years b-e occurs.
For this simply changing the year from 2012 to 1 and then numbering through did the trick.

Many thanks
capt.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial