Given a certain yearly savings, how much can I spend on a capital improvement? NPV of future cash flow

Question

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.

Answer 1

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

Answer 2

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.

Answer 3

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.

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

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 :-

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

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

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

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

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

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.