Question

A couple has just purchased a home for $425,300.00. They will pay 20% down in cash, and finance the remaining balance. The mortgage broker has gotten them a mortgage rate of 3.36% APR with monthly compounding. The mortgage has a term of 30 years.

How much interest is paid in the first year?

Answer 1

w N A B с D E F 1. Payment Beg amount Payment Int. portion Pricipal portion Principal bal. 2 1 $340,240.00 $1,501.36 $ 952.67 $548.69 $ 339,691.31 2 $339,691.31 $1,501.36 $ 951.14 $550.23 $339,141.08 4 3 $339,141.08 $1,501.36 $ 949.60 $551.77 $338,589.31 5 4 $338,589.31 $1,501.36 $ 948.05 $553.31 $338,035.99 6 5 $338,035.99 $1,501.36 $ 946.50 $554.86 $337,481.13 7 6 $337,481.13 $1,501.36 $ 944.95 $556.42 $336,924.71 8 7 $336,924.71 $1,501.36 $ 943.39 $557.98 $336,366.74 9 8 $336,366.74 $1,501.36 $ 941.83 $559.54 $335,807.20 10 9 $335,807.20 $1,501.36 $ 940.26 $561.10 $335,246.10 11 10 $335,246.10 $1,501.36 $ 938.69 $562.68 $ 334,683.42 12 11 $334,683.42 $1,501.36 $ 937.11 $564.25 $ 334,119.17 13 12 $334,119.17 $1,501.36 $ 935.53 $565.83 $333,553.34 14 Int for 1st year $11,329.71

Calc:-

A B С D E F 1 Payment Beg amount Payment Int. portion Pricipal portion Principal bal. 2 1 =425300*80% =PMT(3.36%/12,30*12,-425300*80%) =B2*3.36%/12=C2-D2 =B2-E2 3 2 =B2-E2 =PMT(3.36%/12,30*12,-425300*80%) =B3*3.36%/12 =C3-D3 =B3-E3 4. 3 =B3-E3 =PMT(3.36%/12,30*12,-425300*80%) =B4*3.36%/12 =C4-D4 =B4-E4 5 4 =B4-E4 =PMT(3.36%/12,30*12,-425300*80%) =B5*3.36%/12 =C5-D5 =B5-E5 6 5 =B5-E5 =PMT(3.36%/12,30*12,-425300*80%) =B6*3.36%/12 =C6-06 =B6-E6 7 6 =B6-E6 =PMT(3.36%/12,30*12,-425300*80%) =B7*3.36%/12 =C7-D7 =B7-E7 8 7 =B7-E7 =PMT(3.36%/12,30*12,-425300*80%) =B8*3.36%/12 =C8-D8 =B8-E8 98 =B8-E8 =PMT(3.36%/12,30*12,-425300*80%) =B9*3.36%/12 =C9-D9 =B9-E9 10 9 =B9-E9 =PMT(3.36%/12,30*12,-425300*80%) =B10*3.36%/12 =C10-D10 =B10-E10 11 10 =B10-E10 =PMT(3.36%/12,30*12,-425300*80%) =B11*3.36%/12 =C11-D11 =B11-E11 12 11 =B11-E11 =PMT(3.36%/12,30*12,-425300*80%) =B12*3.36%/12 =C12-D12 =B12-E12 13 12 =B12-E12 =PMT(3.36%/12,30*12,-425300*80%) (=B13*3.36%/12 |=C13-D13 =B13-E13 14 Int for 1st year =SUM(D2:D13)