Engineering Economic Analysis

UsingSpreadsheets to Analyze Loans 191

The first step is to calculate the monthly payment:

A-2400(AjP,lh%, 6) .2400 (0..J.696) ..$407.Q

With tlIi~ iIlformation to p.al).dtQe engiIieer cap. us~. the $pJ;ea<l$he~tofEiguI:e 6-1 to obtafu the

amowzatiQJ.).schedule. '1: =

How Much to Interest? How Much to Principal?

For a loan with constant payments, we can answer these questions for any period without

the full amortization schedule. For a loan with constant payments, the functions IPMT

and PPMT directly answer these questions. For simple problems, both functions have four

arguments(i, t, n, -P), wheretis the time period being calculated. Both functions have

optional arguments that permit adding a balloon payment (anF)and changing from end-

of-period payments to beginning-of-period payments.

For example, consider Period 4 of Example 6-11. The spreadsheet formulas give the.

same answer as shown in Figure 6-1.

Interes4=IPMT(0.5%, 4, 6, -2400) = $6.04

Principalpayment4= PPMT(0.5%,4,6, -2400)=$400.98

Finding the Balance Due on a Loan

Anamortizationschedule can be used to calculate the balance due on a loan. Or more easily

the balance due equals the present worth of the remaining payments. Interest is paid in full

after each payment, so later payments are simply based on the balance due.

I

L

A. caris pUJ.'chasedWith a 48-month, 9% nominal loan with an initial balance of $15,000. What

is the,balance dtl~ halfway through the 4 years?

'J!l

Th,.eW$t~Jep j~JQ;f81q1l1at~ !h.~1J1P!lfuJypay.w.el).t".3-ta mon(hlyinteJ;est;-ate,J>fJ /4%.Jbis ~quals ~W"

"II

,IPaYIIlel).f' '..15,QOO(AJP,'0.75%,48) or. ...PMT(0,75%, 48, -15,000)

:=d! R:. ~{/15',"6(0){6.iQ2];9) ~;$17330~'" bt~ J!.1373;28

-.-