3

I'm seeking a simple online calculator, or explicit instructions for a spreadsheet, for answering simple real-world scenarios like this:

Our insurance company is offering a 30% discount on an $8200/year commercial policy, if we install sprinklers. The insurance is paid in two installments. We assume we can get an equity loan at 5% fixed. With a planning horizon of 20 years, and $400 a year in added inspection cost for the sprinkler, how much money can we spend on the system and break even?

or

We'll save $2060 per year if we install a sprinkler system. The system will cost $40,000. Assuming we can borrow money at 5%, and zero cost for administration of the loan, what's the payback period on the sprinkler system?

The same calculation may apply to energy upgrades or any capital expenditure that results in a future savings. This is not a homework question, and I'm seeking a general answer that would work for a variety of scenarios.

I found many of the online mortgage calculators are poorly set up for this question.

3

This investment does not have a payback period as the net present value of your investment is negative.

Your investment requires an initial cash outlay of $40,000 followed by annual savings of $2060 for the next 20 years. Your discount rate is 5% at which the NPV is $-14327.85 as calculated below by using this JavaScript financial functions library tadJS that is based on a popular tadXL add-in for Excel 2007, 2010 and 2013.

cash flows=[-40000,2060,2060,2060,2060,2060,2060,2060,2060,2060,2060,2060,2060,2060,2060,2060,2060,2060,2060,2060,2060]
tadNPV(5%, [cash flows], 1, 1, 1, 1) = -14327.84669436763
tadPV(5%, 20, -2060)-40000 = -14327.846694367629
  • At what installed price does the investment have a neutral NPV? Given a new bid on costs, how would one recalculate the number of years to fiscal parity? (In the real example underlying this general question the actual economic service life of a new ductile iron sprinkler is well north of 50 years in a building that's already 70 years old. Similar European buildings are over 150 years young and still economically viable). – Bryce Feb 13 '14 at 9:03
  • Note: true fiscal arrangement in the underlying example would likely be a 10-20 year loan but 50-100 year service life of the improvement. – Bryce Feb 13 '14 at 9:09
  • =NPER(5%,2060,-40000) = 72.48 years = 72 years 6 months – user11906 Feb 13 '14 at 10:17
  • =PV(5%, 72.476,-2060) =40,000 – user11906 Feb 13 '14 at 10:18
  • If you are seeking a price of the sprinkler that will break even within 20 years then =PMT(5%,20,-40000) =$3,210 – user11906 Feb 13 '14 at 10:39
0

For this, the internal rate of return is preferred.

In short, all cash flows need to be discounted to the present and set equal to 0 so that an implied rate of return can be calculated.

You could try to work this out by hand, but it's practically hopeless because of solving for roots of the implied rate of return which are most likely complex.

It's better to use a spreadsheet with this capability such as OpenOffice's Calc.

The average return on equity is 9%, so anything higher than that is a rational choice.

Example

Using this simple tool, the formula variables can easily be input.

For instance, the first year has a presumed cash inflow of $2,460 because the insurance has a 30% discount from $8,200 that is assumed to be otherwise paid, a cash inflow of $40,000 to finance the sprinklers, a cash outflow of $40,000 to fund the sprinklers, a $400 outflow for inspection, and an outflow in the amount of the first year's interest on the loan.

This should be repeated for each year. They can be input undiscounted, as they are, for each year, and the calculator will do the rest.

  • Could you be more specific about how to answer the example, using Calc or an online tool? Once I have an implied IRR how does that relate to the management-level question (e.g. "how many dollars can we spend on the sprinklers"? or "how many years until we break even?"). – Bryce Feb 13 '14 at 4:25
  • @Bryce I knew I forgot something. There you go. – user11865 Feb 13 '14 at 4:52
0

The question states :-

Our insurance company is offering a 30% discount on an $8200/year commercial policy, if we install sprinklers. The insurance is paid in two installments. ...

This appears to mean six-monthly payments, so I'll make some comparison calculations using six-monthly loan repayments to keep things simple.

Without the loan or sprinklers the insurance costs $4100 every six months.

Using this loan payment formula, the calculation below shows, with the 30% discounted insurance, sprinkler maintenance and loan repayment, you would be paying $4655.28 every six months.

enter image description here

The discount required to break even is 43.5%. I.e. rearranging the equation :-

enter image description here

Alternatively, with the discount of 30% you would break even if the six-monthly repayment amount was $1030. Solving the payment equation for s gives an equation for the loan :-

enter image description here

So with the 30% discount you would break even if the loan required was $25989.

Checking by back-calculating the periodic payment amount, a :-

enter image description here

Likewise we can keep the loan at $40000 and solve for t to find the break-even loan term :-

enter image description here

(Note, in this formula Log denotes the natural logarithm.) Now we can set some values :-

enter image description here

So with break-even payments the $40000 loan is paid off in just under 65.5 years. I.e. checking :-

enter image description here

This just beats the $4100 cost of proceeding without the sprinklers.

Notes

If your loan repayment was monthly it would reduce the cost of the loan slightly. The periodic interest rate is calculated from the APR according to the method used in the EU and in some cases in US. The calculations above were run using Mathematica.

  • May I ask why you use the nominal interest rate in your calculations rather than the apr - annual effective yield – user11906 Feb 14 '14 at 11:45
  • That's the European method : en.wikipedia.org/wiki/Annual_percentage_rate#European_Union . I'll try to elaborate on that later today. – Chris Degnen Feb 14 '14 at 12:01
  • I understand, so I assume the rate that the banks quote is the APR. Actually I had coded a set of financial functions that work on a different assumption by allowing the user to enter the nominal rates and then the calculations are carried out internally by first finding the APR. See this tadxl.com and the online JavaScript versions of the same here at github.com/FinancialEngineer/tadJS but I guess I would have now make provision for allowing the user the option of entering either the nominal or the effective rates. – user11906 Feb 14 '14 at 13:30
  • Do you manage money for high profile clients in one of the British Isles. I have to get myself a copy of Mathematica as I recall from a discussion last year in which you used the Mathematica software to derive a formula that was hitherto unknown to me and it solved a complex time value of money calculations whereas my own personal solution for such a problem required a traditional sum of series method to solve similar problems. Would it be possible for you to enter the commands in Mathematica to check what it says is the closed form formula for Sigma[k=0 to N-1](1+g)^k.(1+i)^-k-1 – user11906 Feb 14 '14 at 14:33
  • 1
    @AbrahamA. - Hi, yes that's me. Interesting to see your work, Abe. The closed form you asked about is ((1 + g)^n - (1 + i)^n)/((g - i)*(1 + i)^n). I'm a big fan of Mathematica. Since a couple of years ago Wolfram released a very reasonably priced (fully functional) home-use edition. – Chris Degnen Feb 14 '14 at 14:52

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.