Setup the problem in Excel You have been assigned a set of particulars (loan amo

Question

Setup the problem in Excel You have been assigned a set of particulars (loan amount, tenure, and interest rate);

. You will begin by using the PMT function of Excel to calculate the monthly loan repayment (installment) for the particulars provided to you. Next, you create a table in excel that calculates the monthly interest and principal amount to be repaid for each month until the remaining loan balance gets to zero. Solve the problem This can be accomplished in two ways: using goal seek to set the principal outstanding of the last month to zero or to set the monthly instalment to the loan repayment amount calculated earlier. Plot a graph. You need to plot a stacked column chart. You will want the month on the X-axis. The Y-axis will be the monthly payment; each bar must show the interest and principal amount separately. Make sure that your graph is properly labeled and is easy for the reader to understand. Make observations. Look at your table and graph and see what observations you can make. This observation will vary greatly depending upon the particulars of your loan. Answer these questions: • What is the monthly payment for your loan? • How has the composition of the monthly payment (principal and interest) changed over the months?

A.H $100,000.00 10 4.00% B.B $20,000.00 5 3.90% B.T $25,000.00 5 4.30% B.J $250,000.00 20 9.00% B.C $1,000,000.00 15 9.00% B.l $5,000.00 3 3.20% C.E $75,000.00 22 7.50% C.k $50,000.00 14 7.50% f.l $35,000.00 7 6.20% f.t $15,000.00 5 1.90% g.s $22,000.00 5 2.30% g.s $500,000.00 30 12.00% h.s $7,500,000.00 25 6.00% h.b $2,200,000.00 23 6.00% h.a $390,000.00 22 6.00% j.s $2,000,000,000.00 40 12.00% k.r $320,000.00 10 7.75% k.a $270,000.00 10 6.55% l.k $47,000.00 12 6.00% L.j $98,500.00 12 6.00% m.a $32,000,000.00 22 6.00% m.l $75,000,000.00 18 7.50% n.l $6,200,000.00 11 6.60% o.p $43,724.00 11 6.00% o.a $38,010.00 5 6.50% p.s $250,000,000.00 17.5 6.00% p.a $300,000.00 19 7.50% p.b. $200,000.00 18 7.50% r.k $1,000,000.00 20 6.60% r.v. $1,100,000,000.00 27 9.00% s.k $250,000.00 15 4.50% w.k $120,000.00 12 6.60% t.t $30,000.00 6 7.00% tj $50,000.00 18 7.50% w.r $1,000,000.00 22 3.90% w.m $290,000.00 25 9.20% w.j $600,000.00 30 4.25% w.k $350,000,000.00 60 3.20% w.l $30,000.00 5 6.50%

Explanation / Answer

To calculate the monthly intalnment by PMT , the formula is , PMT(rate,npr,PV) where rate is the rate of interest, npr is the total number of payments in installments and PV is the present value or the principal amount.

So using the above formula in excel , monthly installment for each loan is calculated as follows:

Every month for each of the installments the principal amount will be increasing and interst amount will decrease.

i.e, for each monthly installments going forward the interset amount goes from high to low and the principal component from lower to higher.

Loan maount Tenure in months Interest Rate Monthly instalment .H $100,000.00 10 4.00% ($12,329.09) B.B $20,000.00 5 3.90% ($4,479.93) B.T $25,000.00 5 4.30% ($5,663.09) B.J $250,000.00 20 9.00% ($27,386.62) B.C $1,000,000.00 15 9.00% ($124,058.88) B.l $5,000.00 3 3.20% ($1,774.45) C.E $75,000.00 22 7.50% ($7,064.02) C.k $50,000.00 14 7.50% ($5,889.87) f.l $35,000.00 7 6.20% ($6,314.37) f.t $15,000.00 5 1.90% ($3,173.15) g.s $22,000.00 5 2.30% ($4,708.20) g.s $500,000.00 30 12.00% ($62,071.83) h.s $7,500,000.00 25 6.00% ($586,700.39) h.b $2,200,000.00 23 6.00% ($178,812.67) h.a $390,000.00 22 6.00% ($32,387.77) j.s $2,000,000,000.00 40 12.00% ($242,607,251.17) k.r $320,000.00 10 7.75% ($47,153.07) k.a $270,000.00 10 6.55% ($37,646.20) l.k $47,000.00 12 6.00% ($5,606.02) L.j $98,500.00 12 6.00% ($11,748.79) m.a $32,000,000.00 22 6.00% ($2,657,458.19) m.l $75,000,000.00 18 7.50% ($7,727,171.84) n.l $6,200,000.00 11 6.60% ($810,416.94) o.p $43,724.00 11 6.00% ($5,543.89) o.a $38,010.00 5 6.50% ($9,146.52) p.s $250,000,000.00 17.5 6.00% ($23,463,199.89) p.a $300,000.00 19 7.50% ($30,123.27) p.b. $200,000.00 18 7.50% ($20,605.79) r.k $1,000,000.00 20 6.60% ($91,478.59) r.v. $1,100,000,000.00 27 9.00% ($109,708,395.94) s.k $250,000.00 15 4.50% ($23,278.45) w.k $120,000.00 12 6.60% ($14,787.78) t.t $30,000.00 6 7.00% ($6,293.87) tj $50,000.00 18 7.50% ($5,151.45) w.r $1,000,000.00 22 3.90% ($68,538.99) w.m $290,000.00 25 9.20% ($30,003.59) w.j $600,000.00 30 4.25% ($35,758.95) w.k $350,000,000.00 60 3.20% ($13,193,294.23) w.l $30,000.00 5 6.50% ($7,219.04)

Related Questions

Navigate

• Browse (All)
• Browse S
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.