Engineering Economic Analysis

190 ANNUAL CASH FLOW ANALYSIS

Using Spreadsheets to Analyze Loans

Loan and bond payments are made by firms, agencies, and individual engineers. Usually,

the payments in each period are constant. Spreadsheetsmake it easy to:

· Calculate the loan's amortization schedule

· Decide how a payment is to be split between principal and interest

·Find the balance due on a loan

·Calculate the number of payments remaining on a loan.

Building an Amortization Schedule

As illustrated in Chapter 4 and Appendix 1, an amortizationschedule lists for each payment

period: the loan payment, interest paid, principal paid, and remaining balance. For each

period the interest paid equals the interest rate times the balance remaining from the period'

before. Then the principal payment equals the payment minus the interest paid. Finally,

this principal payment is applied to the balance remaining from the' preceding period to

calculate the new remaining balance. As a basis for comparison with sprea~sheet loan

functions, Figure 6-1 shows this calculation for Example 6-11.

An engineer wanted to celebrate graduating and getting a job by buying $2400 of new furniture.

Luckily the store was offering 6-month financing at thelow interest rate of 6% per year nominal'

(really 1/2%per month). Calculate the amortization schedule.

~: ~:; ~ ~

.p

I

.

t'" '1'1~'FIGURE'6:r AIDomzati6n schedrile:forfUnllture1oah.

..,.~. ..;.

I

j

I

L ,'--"

, ~"1 __ - .. ---

A B C D E

1 2400 Initial balance

2 0.50% i

3 6 N

(^4) $407.03 Payment ""'.--PM)'(A2,A3,Al)
5
(^6) Principal El11:ling ,
7 Month Interest Payment Balance
(^80) , 240"0.00 #Al
(^91) , 12.00 395.03 2004.97 ==D8--C9
10 2 10.02 397.00 1607.97
(^113) 8;04 398.99 1208.98
(^124) I 6.04 400.98 807.99
13 5 4.04 402,99 I 405.00 ,
(^146) 2.03 405.00 0.00
15
,
.

. '",. ....
"k'''' ,, ,

(^16) .,"'" ,"".. ....... '.0 .= ,",' .' <;;J'!i;==
(^17). ==$A$2Dl3 .' '.
18 ,,>.. '. .. "
""'==,." ;;.. :;;;:11:1: