SCENARIO $1$: Base Case and Amortization Schedule for a $30-$year Loan

Calculate the monthly rate that will be used to pay your loan.

Number of Periods

What is the Effective Annual Rate $($EAR$)?$

Hint: Use the EFFECT $($Nominal$\_$rate, Npery$)$ function:

Nominal$\_$rate $($nominal interest rate, APR$)=$ B$11$

$,$ Npery $($number of compounding periods per year$)=12$

Monthly Payment

Hint: Use the PMT $($Rate$,$ Nper, Pv$,$ Fv$,$ Type$)$ function:

$,$ Rate $=$ Monthly Rate cell B$12)$

$,$ Nper $($Number of Periods$)=$ B$9$

Pv $($present value$)=-B7($The negative loan amount represents a cash outflow.$)$

Fv $($future value$)=0($The debt is fully paid off at the end.$)$

Type $($payment timing$)=0$ or omitted $($for end$-$of$-$period payments$)$

Construct a Loan Amortization Table $($considering monthly payments$).$

How much will you pay in interest over the life of the loan $($i$.$e$.,$ total interest paid$)?$

What is the principal balance remaining $($i$.$e$.,$ Ending Balance$)$ halfway through the loan term $(15$ years$)?$

Scenario Analysis

To save time and effort, the easiest way to do this is to copy your entire worksheet onto

Base Case: a $30-$year Loan

MAKE SURE TO REFERENCE CELLS! DO NOT RETYPE THE INFORMATION.

Home Amount

Down Payment $\%$

$25\%$

Loan Amount

Term $($yrs$)$

$30$

Number of Periods

FV

$0$

APR

$($use $30-$year fixed mortgage rate$)$

Question $2$: Wr

Answer:

Monthly Rate

EAR

$(1$ pt$)$

Monthly Payment

$(3$ pts$)$

Total Interest paid

$(3$ pts$)$

Question $1$: Wr

Answer:

Question $2$: Wr

Answer:

Loan Amortization Table

Period Beginning Balance

Monthly Payment

Interest Paid

Principal Paid

Assignment Rubric

Your Home Picture

Base Case &