Engineering Economic Analysis

192 ANNUAL CASH FLOW ANALYSIS

The next step will use ~e spreadsheet answer, because it is more accurate (there are only three

significant digits in the tabulated factor).

After 24 payments and with 24 left, the remainingbalance equals(P/A, i,Nremaining)payment

Balance=(P/A,0.75%, 24)$373.28 or

=(21.889)(373.28) =$8170.73 or

= PV(0.75%, 24,373.28)

.$8170.78

Thus halfway through the repayment schedule, 54.5% of the original balance is still owed.

!.

Pay Off Debt Sooner by Increasing Payments

Paying off debt can be a good investment because the investment earns the rate of interest

on the loan. For example, this could be 8% for a mortgage, 10% for a car loan, or 19%

for a credit card. When making extra payments on a loan, the common question is: How

much sooner will the debt be paid off? Until the debt is paid off, any early payments are

essentially locked up, since the same payment amount is owed each month.

The firstreason that spreadsheetsare convenientis fractional interestrates. For example,

an auto loan might be at a nominal rate of 13% with monthly compounding or 1.08333%

per month. The second reason is that the function NPER calculates the number of periods

remaining on a loan.

NPER can be used to calculate how much difference one extra payment makes or

how much difference increasing all payments byx% makes. Extra payments are applied

entirely to principal, so the interest rate, remaining balance, and payment amounts are all

known. Nremainingequals NPER(i, payment, remaining balance) with optional argumentsfor

beginning-of-period cash flows and balloon payments. The signs.of the payment and the

remaining balance must be different.

Maria has a 7.5% mortgage with monthly payments for 30 years. Her original balance was

$100,000, and she just made her twelfth payment. Each month she also pays into a reserve

account, which the bank uses to pay her fire and liability insurance ($900 annually) and property

taxes ($1500 annually). By how much does she shorten the loan if she makes an extraloan

payment today? If she makes an extratotalpayment? If she increases each total payment to 110%

of her current total payment?

SOLUTION

The first step is to calculate Maria'sloanpayment for the 360 months. Rather than calculating a

six.-significantdigit monthly interest rate,it is easier to use 0.075/12 in the spreadsheetformulas.

Payment-CPMT(O.01s/12,360, -100000) = $699.21

...- - - - ...- - - - --