You purchase a fully loaded Honda Accord with an MSRP of $$32,000$ for $$27,000.$You pay the $3\%$ tax of $$810$ up front and put down $$5,000.$ The dealer offers a simple interest installment loan with an annual rate of $5\%$ for $3$ years. The projected resale value of the car after $2$ years is $$17,000.$

Questions

$1)$ Compute monthly loan payments

$3$ steps:

$1)$ Compute Loan Value

Purchase Price $($Given$)$

$-$ Down Payment $($Given$)$

$=$ Loan Value

$2)$ Convert annual loan term to # of months and interest rate to monthly

Interest rate $($should be in decimal form$)$

$3)$ Use Excel formula to compute monthly payment

$=-$ PMT$($rate$,$ period, PV$)$

Pull these from steps $1$ and $2$

$2)$ Create monthly amortization schedule

$10$ steps:

$1)$ Enter column headings:

Beginning Balance $=$BB

Monthly Payment $=$ PMT

Monthly Interest $=$ INT

Monthly Principal Repaid $=$ PRINC

Ending Balance $=$ EB

$2)$ Enter months by #

$1$ in first row; $=$ first row cell $+1$ for rest

$3)$ Enter first BB $($pull loan value from question $1)$

$4)$ Enter first PMT $($pull from question $1$; anchor row using $$)$

$5)$ Compute first INT: Multiply BB by Monthly interest rate $($from Question

$1$; anchor using $$)$

$6)$ Compute first PRINC: $=$PMT $$ INT

$7)$ Compute first EB: $=$BB $$ PRINC

$8)$ Copy down first row until end of loan term

$9)$ Go to second row of amortization schedule, first column $($BB$)$ & pull EB

from first row. Copy this formula down to end of loan term

Check to be sure EB in last month of loan term $=0$

$10)$ Enter SUM formula at bottom to sum PMT$,$ INT, PRINC columns

Check to be sure PRINC sum $=$ Loan Value from Question $1$

$3)$ Compute total interest

Use sum of INT column computed in Question $2$

$4)$ Compute total cash outflow

Down Payment

$+$ Total of PMT column $($from Question $2)$

$+$ Up front tax payment

$=$ Total Cash Outflow

$5)$ Compute amount you would net after selling car at $2$ years

Sale Price at End of Yr $2($Given$)$

$-$ Loan Balance Remaining at end of Yr $2($EB in Mo $24)$

$=$ Amount Netted From Selling Car at End of Yr $2$

$6)$ Does the principal portion of the payment increase or decrease over

time?

Review the PRINC column

$7)$ Does the interest portion of the payment increase or decrease over time?

Review the INT column