Question

please Help! You borrow money on a self liquidating installement loan (equal payments at the end of each year, each payment is part principal part interest) I also need the excel formulas . Please do it on excel.

You bomow money on a self liquidating instalent loan (equal payments at the end of each year, each payment is part principal part interest Loan $312,000 Interest Rate 13 30% Life years) Date of Loan January 1, 2020 Use the installment method - not straight line Do NOT round any intermediate numbers Do NOT turn this into a monthly problem. What is the annual payment? b) What are the total interest payments? 5 c) After 28 payments have been made, what percentage of the total interest has been paid round to the nearest percentage point? 6 d) Alter 28 payments have been made, what percentage of the total principal has been paid round to the nearest percentage point)? 8 Redo the problem the interest rate is 3.00 20 or a well designed spreadsheet this should take 30 seconds) 22 ) What is the annual payment? 23 ) What are the total interest payments? 24 After 28 payments have been made, what percentage of the total interest has been paid round to the nearest percentage point)? 25 h) After 28 payments have been made what percentage of the total principal has been paid found to the nearest percentage point? Qloan 23-59073 S heet3 + Ready Q W ER TY

Answer 1

13.30% 48 Annual Interest Rate Years Payments Per Year Amount $312,000 now let us design a loan schedule using below formulas: Payment =PMT(AnnualInterestRate/PaymentsPerYear, Years*PaymentsPerYear,Amount) Principal =PPMT(AnnuallnterestRate/PaymentsPerYear, Year, Years*PaymentsPer Year, Amount) Interest =IPMT(AnnuallnterestRate/PaymentsPerYear, Year, Years*PaymentsPerYear,Amount) Balance =Amount+Principal

00 OU AWN 13 14 18 20 21 22. 23 Year Payment Principal Interest Balance ($41,599.77) ($103.77) ($41,496.00) $311,896.23 2 ($41,599.77) ($117.57) ($41,482.20) $311,778.67 3 ($41,599.77) ($133.20) ($41,466.56) $311,645.46 ($41,599.77) ($150.92) ($41,448.85) $311,494.54 5 ($41,599.77) ($170.99) ($41,428.77) $311,323.55 6 ($41,599.77) ($193.73) ($41,406.03) $311,129.82 7 ($41,599.77) ($219.50) ($41,380.27) $310,910.32 ($41,599.77) ($248.69) ($41,351.07) $310,661.63 9 ($41,599.77) ($281.77) ($41,318.00) $310,379.86 10 ($41,599.77) ($319.25) ($41,280.52) $310,060.61 11 ($41,599.77) ($361.70) ($41,238.06) $309,698.91 12 ($41,599.77) ($409.81) ($41,189.95) $309,289.09 ($41,599.77) ($464.32) ($41,135.45) $308,824.78 ($41,599.77) ($526.07) ($41,073.70) $308,298.71 15 ($41,599.77) ($596.04) ($41,003.73) $307,702.67 16 ($41,599.77) ($675.31) ($40,924.45) $307,027.36 17 ($41,599.77) ($765.13) ($40,834.64) $306,262.23 ($41,599.77) ($866.89) ($40,732.88) $305,395.34 19 ($41,599.77) ($982.19) ($40,617.58) $304,413.16 ($41,599.77) ($1,112.82) ($40,486.95 $303,300.34 ($41,599.77) ($1,260.82) ($40,338.95) $302,039.52 ($41,599.77) ($1,428.51) ($40,171.26) $300,611.01 ($41,599.77) ($1,618.50) ($39,981.26) $298,992.51 24 ($41,599.77) ($1,833.76) ($39,766.00) $297,158.74 25 ($41,599.77) ($2,077.65) ($39,522.11) $295,081.09 26 ($41,599.77) ($2,353.98) ($39,245.78) $292,727.11 ($41,599.77) ($2,667.06) ($38,932.71) $290,060.05 ($41,599.77) ($3,021.78) ($38,577.99) $287,038.27 29 ($41,599.77) ($3,423.68) ($38,176.09) $283,614.59 ($41,599.77) ($3,879.03) ($37,720.74) $279,735.57 ($41,599.77) 494) ($37,204.83) $275,340.63 32 ($41,599.77) ($4,979.46) ($36,620.30) $270,361.17 33 ($41,599.77) ($5,641.73) ($35,958.04) $264,719.44 ($41,599.77) ($6,392.08) ($35,207.69) $258,327.36 35 ($41,599.77) ($7,242.23) ($34,357.54) $251,085.13 ($41,599.77) ($33,394.32) $242,879.69 ($41,599.77) ($9,296.77) ($32,303.00) $233,582.92 38 ($41,599.77) ($10,533.24) ($31,066.53) $223,049.68 ($41,599.77) ($11,934.16) ($29,665.61) $211,115.53 40 ($41,599.77) ($13,521.40) ($28,078.36) $197,594.12 ($41,599.77) ($15,319.75) ($26,280.02) $182,274.38 ($41,599.77) ($17,357.27) ($24,242.49) $164,917.10 ($41,599.77) ($19,665.79) ($21,933.97) $145,251.31 ($41,599.77) ($22,281.34) ($19,318.42) $122,969.97 45 ($41,599.77) ($25,244.76) ($16,355.01) $97,725.21 46 ($41,599.77) ($28,602.31) ($12,997.45) $69,122.90 47 ($41,599.77) ($32,406.42) ($9,193.35) $36,716.47 48 ($41,599.77) ($36,716.47) ($4,883.29) $0.00 Totals $1,996,788.77 $312,000.00 $1,684,788.77 27 28 31 ($4.3 ($8.2 15.44) 36 37 39 41 42 A2 44

A) annual payment per year = ac calculated above ($41,599.77) B) total interest payment sum of interest column ($1,684,788.7 $1,684,788.77 $1,139,831.72 C) after 28 payments, % of interest Total interest Interest upto 28th payment (sum of interest column upto year 28) % of interest paid (1139831.72/1684788.77)*100 67.65 $312,000.00 $24,961.73 D) after 28 payments, % of principal Total Principal Interest upto 28th payment (sum of interest column upto year 28) % of interest paid (24961.73/312000)*100
now you have ready temple, for second part of the question, you just have to change "annual interest rate" from 13.30% to 3%.

3.00% 48 Annual Interest Rate Years Payments Per Year Amount $312,000 now let us design a loan schedule using below formulas: Payment =PMT(AnnuallnterestRate/PaymentsPer Year, Years*PaymentsPerYear, Amount) Principal =PPMT(AnnuallnterestRate/PaymentsPerYear, Year, Years*PaymentsPerYear, Amount Interest =IPMT(AnnuallnterestRate/PaymentsPerYear, Year, Years*PaymentsPerYear, Amount) Balance =Amount+Principal

Year Payment Principal Interest Balance 1 ($12,348.27) ($2,988.27) ($9,360.00) $309,011.73 2 ($12,348.27) ($3,077.91) ($9,270.35) $305,933.82 3 ($12,348.27) ($3,170.25) ($9,178.01) $302,763.57 4 ($12,348.27) ($3,265.36) ($9,082.91) $299,498.21 5 ($12,348.27) ($3,363.32) ($8,984.95) $296,134.89 6 ($12,348.27) ($3,464.22) ($8,884.05) $292,670.67 7 ($12,348.27) ($3,568.15) ($8,780.12) $289,102.53 8 ($12,348.27) ($3,675.19 ($8,673.08) $285,427.34 9 ($12,348.27) ($3,785.45) ($8,562.82) $281,641.89 10 ($12,348.27) ($3,899.01) ($8,449.26) $277,742.89 11 ($12,348.27) ($4,015.98) ($8,332.29) $273,726.91 12 ($12,348.27) ($4,136.46) ($8,211.81) $269,590.45 13 ($12,348.27) ($4,260.55) ($8,087.71) $265,329.90 14 ($12,348.27) ($4,388.37) ($7,959.90) $260,941.53 15 ($12,348.27) ($4,520.02) ($7,828.25) $256,421.51 16 ($12,348.27) ($4,655.62) ($7,692.65) $251,765.89 17 $12,348.27) ($4,795.29) ($7,552.98) $246,970.60 18 ($12,348.27) ($4,939.15) ($7,409.12) $242,031.45 19 ($12,348.27) ($5,087.32) ($7,260.94) $236,944.13 20 ($12,348.27) ($5,239.94) ($7,108.32) $231,704.19 21 ($12,348.27) ($5,397.14) ($6,951.13) $226,307.05 22 ($12,348.27) ($5,559.05) ($6,789.21) $220,748.00 23 ($12,348.27) ($5,725.83) ($6,622.44) $215,022.17 24 ($12,348.27) ($5,897.60) ($6,450.67) $209,124.57 25 ($12,348.27) ($6,074.53) ($6,273.74) $203,050.04 26 ($12,348.27) ($6,256.76) ($6,091.50) $196,793.28 27 ($12,348.27) ($6,444.47) ($5,903.80) $190,348.81 28 ($12,348.27) ($6,637.80) ($5,710.46) $183,711.01 29 ($12,348.27) ($6,836.94) ($5,511.33) $176,874.07 ($12,348.27) ($7,042.04) ($5.306.22) $169,832.03 ($12,348.21) ($7,253.30) ($5,094.96) $162,578.73 32 ($12,348.27) ($7,470.90) ($4,877.36) $155, 107.82 33 $12,348.27) ($7,695.03) ($4,653.23) $147,412.79 34 ($12,348.27) ($7,925.88) ($4,422.38) $139,486.91 35 ($12,348.27) ($8,163.66) ($4,184.61) $131,323.25 36 ($12,348.27) ($8,408.57) ($3,939.70) $122,914.68 37 ($12,348.27) ($8,660.82) ($3,687.44) $114,253.86 38 ($12,348.27) ($8,920.65) ($3,427.62) $105,333.21 39 ($12,348.27) ($9,188.27) ($3,160.00) $96,144.94 40 ($12,348.27) ($9,463.92) ($2,884.35) $86,681.02 41 ($12,348.27) ($9,747.83) ($2,600.43) $76,933.19 42 ($12,348.27) ($10,040.27) ($2,308.00) $66,892.92 43 ($12,348.27) ($10,341.48) ($2,006.79) $56,551.44 44 ($12,348.27) ($10,651.72) ($1,696.54) $45,899.72 45 ($12,348.27) ($10,971.27) ($1,376.99 $34,928.44 46 ($12,348.27) ($11,300.41) ($1,047.85) $23,628.03 47 ($12,348.27) ($11,639.42) ($708.84) $11,988.61 48 ($12,348.27) ($11,988.61) ($359.66) $0.00 Totals $592,716.74 $312,000.00 $280,716.74

A) annual payment per year = ac calculated above ($12,348.27) B) total interest payment sum of interest column ($280,716.74 $280,716.74 $217,462.44 C) after 28 payments, % of interest Total interest Interest upto 28th payment (sum of interest column upto year 28) % of interest paid (217462.44/280716.74)*100 77.47 $312,000.00 $128,288.99 D) after 28 payments, % of principal Total Principal Interest upto 28th payment (sum of interest column upto year 28) % of interest paid (128289/312000)*100 41.12