Question

5. Kaleen now wishes to finalize the Amortization schedule. In cell J4, create a formula without using a function that subtracts the value in cell I4 from the value in cell H4 to determine how much of the mortgage principal is being paid off each year. Copy the formula in cell J4 to the range J5:J18.

britt ct BE - Comments AutoSave C H 2 File Home Insert X Cut Paste La Capy ~ Format Painter chibuard S C_EX16 41 Andreal arrick 2 - Excel Search Page Layout Formulas Data Review View Help Calibri 11 AA === > Wrap Text Accounting BIU- - -A 3 Merge a Center - $ -% Font s Alignment 5 Number AutoSum 47 Sort & Filter 0 find & Select Share 4 Ideas S8 Insert Delete Termal Formatting Table Styles 5 Styles g Ideas A D E F G H J K L M N O P Q R B C Camp Millowski 5.750% Year Interest Paid 2 3 Date 4 item 5 Price 6 Downı Payrnent 7 Loan Amount $ $ $ Mortgage Loan Payment Calculator 15-Auf-18 Rate Campground Term (Years) 486,000.00 Monthly Payment 97,200.00 Total Interest 388,800.00 Total Cost $ $ $ 3.228.63 192,354.20 678.351.20 10 Rate 7 $ 12 Amortization Schedule Beginning Balance Ending Balance Paid on Principal 1S 388,800.00 S 371,973.53 2 S 371.973,53 S 354,153.63 3 $ 354,153.63 $ 335,281.65 1 $ 335.281.65 $ 315,295.17 5 $ 315,295.47 $ 294,129.30 294,129.30 $ 271,713.49 271.713.49 S 247,974.26 8 $ 247,974.26 $ 222,833.46 9 S 222,833.46 $ 196,208.36 10 $ 196,208.36 S 168,011.31 11 $ 168,011.31 $ 138,149.52 12 $ 138,119.52 $ 106,5211.69 13 $ 106,524.69 $ 73,032.73 14 $ 73,032.73 S 37,563.42 15 $ 37,563.42 S Subtotal $ . Down Payment Total Cost Varying Interest Rate Schedule Monthly Payment Total Interest 3.228.63 $ 192,754.20 3.500% 2,779.46 111,503.40 3.750% 2,827,44 120.129.35 4.000% 2,875.91 128,863.20 1.250% 2,924.86 137,674.52 4.500% 2,974.29 146,572.90 4.750% 3,024.21 155,557.89 5.000% 3,074.61 164,629.01 5.250% 3,125.46 173.795.78 5.500% 3,176.82 183,027.68 5.750% 3,228.63 192.354,20 6.000% 3,280.92 201,764.76 6.250% 3,133.66 211.258.82 6.500% 3,386.87 220,885.78 6.750% 3.110.53 230,195.01 7.000% 3,494.64 240,235.98 13 14 Total Cost $ 678,354.20 597,503.40 606,139.35 614,863.20 623,674.52 532,572.90 641,557.89 650,629.01 659,785.78 669,027.68 578,354.20 687,764.76 647,258.82 706,835.78 716,495.04 726,235.98 $ Documentation Campground Mortgage 6 Ready - - EI 2 100% - 6 :28 PM Type here to search O e 9 11/8/2019

I cant figure out the formula can someone help?

Answer 1

Paid on Principal = Beginning Balance – Ending Balance

Hence, the formula is:

J4 = H4 – I4

Copy this formula for the entire range J6:J18.