Question

Table 5.2 Loan Calculation & Amortization Table PV = Loan Amoul $50,000.00 k = Rate Per Year Per Month n = Term Years Months PMT Use Excel built-in formula Period PMT interest Principal Balance $50,000.00 Total I 5.2 N

1. In Cell D8 create formula PMT=PV/((1-1/(1+k)^n)/k), to calculate the periodic payment on a loan.

2. In cell F8 use built-in function =PMT(k,n,PV). You should get identical answers.

3. Create amortization table (use absolute and relative addressing where appropriate).

4. Print worksheet.

5. Change loan amount and the rate (everything should adjust automatically) and print it again.

6. Print the cell formulas (force to one page).

7. Write report and explain all formulas and procedures.

8. Submit four printouts.

PMT=$1589.99 per month=$1589.99

Rate per month=0.75%

36 months

Total=$57239.52

Total Interest=$7239.52

Principal=$50000.00

Answer 1

Hello,

Please see below snapshot. I have hidden few rows as formulas are similar and one snapshot can give entire solution.

Hope this helps.

Table 5.2 Loan Calculation &Amortization Table PV = Loan Amount $50,000 k = Rate 9% n = Term 3 Period PMT Interest 2 $1,589.99 $1,589.99 $1,589.99 $1,589.99 $1,589.99 $1,589.99 $1,589.99 $1,589.99 $1,589.99 $1,589.99 $1,589.99 $57,239.52 $375.00 $365.89 $356.71 $347.46 $338.14 $69.71 $58.31 $46.82 $35.24 $23.58 $11.84 $7,239.52 Principal Balance $50,000.00 $1,214.99 $48,785.01 $1,224.10 $47,560.91 $1,233.28 $46,327.63 $1,242.53 $45,085.11 $1,251.85 $43,833.26 $1,520.28 $7,774.14 $1,531.68 $6,242.46 $1,543.17 $4,699.29 $1,554.74 $3,144.55 $1,566.40 $1,578.15 $1,578.15 $0.00 $50,000.00 35 36 Total

Table 5.2 Loan Calculation &Amortization Table PV = Loan Amount k = Rate n = Term 50000 0.09 Period PMT Interest Principal Balance =D3 =B8+1 =B9+1 =B10+1 =B11+1 =PMT(D4/12,D5*12,-D3) =C9 =C10 =C11 =B12+1 =C12 =C38 =F8*$D$4/12 =F9*$D$4/12 =F10*$D$4/12 =F11*$D$4/12 =F12*$D$4/12 =F38*$D$4/12 =F39*$D$4/12 =F40*$D$4/12 =F41*$D$4/12 =F42*$D$4/12 =F43*$D$4/12 =SUM(D9:044) =C9-09 =C10-D10 =C11-D11 =C12-D12 =C13-D13 =C39-D39 =C40-D40 =C41-D41 =C42-D42 =C43-143 =C44-044 =SUM(E9:E44) =B38+1 =B39+1 =B40+1 =B41+1 =B42+1 =B43+1 Total =F8-E9 =F9-E10 =F10-E11 =F11-E12 =F12-E13 =F38-E39 =F39-E40 =F40-E41 =F41-E42 =F42-E43 =F43-E44 =C39 =C40 =C41 =C42 =C43 =SUM(C9:044)