MODELING DEBT - LOAN TERMS

Question 1

PMT

Answer 1

CALCULATES CONSTANT LOAN PAYMENT

Question 2

PMT - FORMULA

Answer 2

=pmt(periodic rate, total periods, -loan amount, $ fv)

Question 3

IPMT

Answer 3

CALCULATES INTEREST PAYMENT FOR THE PERIOD

Question 4

IPMT - FORMULA

Answer 4

=ipmt(periodic rate, period, total periods, loan amount)

Question 5

PPMT

Answer 5

CALCULATES PRINCIPAL PAYMENT FOR PERIOD OF INTEREST

Question 6

PPMT - FORMULA

Answer 6

=ppmt(periodic rate, period, total periods, loan amount)

Question 7

CUMIPMT

Answer 7

CALCULATES CUMULATIVE INTEREST PAID FOR PERIODS OF INTEREST

Question 8

CUMIPMT - FORMULA

Answer 8

=cumipmt(periodic rate, total periods, loan amount, beg period, end period, 0)

*LOAN AMOUNT MUST REMAIN POSITIVE FOR CALCULATION TO WORK

Question 9

CUMPRINC

Answer 9

CUMULATIVE PRINCIPAL PAID FOR PERIODS OF INTEREST

Question 10

CUMPRINC - FORMULA

Answer 10

=cumprinc(periodic rate, total periods, loan amount, beg period, end period, 0)

**LOAN AMOUNT MUST REMAIN POSITIVE TO CALCULATE CORRECTLY

Question 11

REMAINING LOAN BALANCE - FORMULA

Answer 11

= ORIGINAL BALANCE - CUMULATIVE PRIN PAID

Question 12

REMAINING LOAN BALANCE - CUMPRINC

Answer 12

CUMULATIVE PRINCIPAL FOR REMAINING LOAN TERM

Question 13

LOAN TERM VS AMORTIZATION TERM

Answer 13

LOAN TERM - PERIOD OF TIME LOAN WILL BE PAID BEFORE BECOMING DUE

AMORTIZATION TERM - TIME PERIOD USED TO CALCULATE LOAN PAYMENTS

Question 14

2 LOAN AMORTIZATION FACTORS

Answer 14

IO PERIOD

LOAN TERM

Question 15

LOAN BALANCE DEPENDS ON

Answer 15

LOAN TERM

Question 16

LOAN BALANCE & LOAN TERM

Answer 16

IF ANNUALIZED PMT PERIOD > LOAN TERM; LOAN BALANCE = $0

Question 17

PRINCIPAL PMT DEPENDS ON

Answer 17

IO PERIOD

LOAN TERM

Question 18

PRINCIPAL PMT & IO PERIOD + LOAN TERM

Answer 18

IF PMT PERIOD > IO PERIOD & IF ANNUALIZED PMT PERIOD <= LOAN TERM; CALCULATE PRIN PMT

Question 19

AMORTIZATION TABLE - ORDER OF CALCULATIONS

Answer 19

BEGINNING BALANCE

PRINCIPAL PAYMENT

INTEREST PAYMENT

TOTAL PAYMENT

ENDING BALANCE

Question 20

BEG BALANCE - AFTER PERIOD 1

Answer 20

=IF(ANNUALIZED PMT PERIOD > LOAN TERM YRS,$0; PRIOR PERIOD END BAL)

Question 21

ANNUALIZED PMT PERIOD

Answer 21

= PMT PERIOD / 12

Question 22

PRIN PMT - FORMULA

Answer 22

=IF(AND(PMT MONTH > IO MONTHS, ANNUALIZED PMT PERIOD <= LOAN TERM), PPMT, 0)

Question 23

PPMT CALCULATION - PMT PERIOD

Answer 23

(PMT MONTH - IO MONTHS)

SUBTRACTING IO MONTHS FROM PMT MONTH TELLS EXCEL TO CALCULATE PRINCIPAL PMT STARTING AT THE CORRECT MONTH AFTER THE IO PERIOD ENDS

EX: IF PMT MONTH IS 13 & IO PERIOD IS 12 MONTHS (13 - 12 = 1); THE PRINCIPAL PMT CALCULATED FOR MONTH 13 WOULD BE BASED ON PRINCIPAL PMT THAT SHOULD BE MADE FOR PERIOD 1 OF AMORTIZING LOAN

Question 24

INTEREST PATMENT - FORMULA

Answer 24

= BEG BALANCE X PERIODIC RATE

Question 25

TOTAL PAYMENT - FORMULA

Answer 25

= PRINCIPAL PMT + INTEREST PMT

Question 26

ENDING BALANCE - FORMULA

Answer 26

= BEG BALANCE - PRINCIPAL PMT

Question 27

BALLOON PMT - FORMULA

Answer 27

=VLOOKUP(LOAN TERM X 12 MONTHS, TABLE ARRAY, END BAL COLUMN #, 0) \*\*\*MULTIPLYING LOAN TERM X 12 PINPOINTS THE LAST PAYMENT MONTH FOR THE LOAN; THIS IS WHERE THE BALLOON BALANCE WILL EXIST