Pending the deal with Nagirok, ABC Co. would like to retire some high interest d

Question

Pending the deal with Nagirok, ABC Co. would like to retire some high interest debt it has with another lender. Currently, ABC Co. has 7 years remaining on a 10 year loan. ABC Co current lender has agreed to accept the present value (PV) of the loan as payoff. Create an amortization schedule that shows how much ABC Co must pay at the end of each of the ten years. Create a worksheet that includes the beginning and ending balance, the amount paid on the principle, and the interest paid for years four through ten.

You’ll need the following info to create the amortization schedule:

-Interest rate: 4.63%

-monthly payment: $4,488

Create a spreadsheet with the following cells and information for the loan details above. Note: you will have to determine the original loan amount.

This is an example:

How would I get these answers, and what formulas would i use in excel to find each total? any help please.

Loan Amount $243,141.25 Interest Rate 4.33% Monthly Payment $2,500 Year Beginning Balance End of Year Balance Principle Interest 1 $243,141.25 223,278.14 19,863.11 10,136.89 2 223,278.14 202,537.68 20,740.46 9,259.54 3 202,537.68 180,861.13 21,656.56 8,343.44 4 180,881.13 158,268.01 22,613.12 7,386.88 5 158,268.01 134,656.07 23,611.94 6,388.06 6 134,656.07 110,001.20 24,654.87 5,345.13 7 110,001.20 84,257.33 25,743.87 4,258.13 8 84,257.33 57,376.36 26,880.97 3,119.03 9 57,376.36 29,308.06 28,068.30 1,931.70 10 29,308.06                                  - 29,308.06 691.94

Explanation / Answer

Answer:

The principal amount is the present value of the Loan repayments at the beginning of the repayment schedule:

Present value of the Loan

= Monthly payment[{(1+Interest rate per month)no of months - 1)/Interest rate per month*(1+Interest rate per month)no of months]

= $4,488 [{(1+0.0463/12 )10*12-1}/{0.0463/12*(1+0.0463/12 )10*12]

= $4,488 (0.587418/0.006125) = $430,436.67

Schedule:

Working:

End of month balance = Beginning Month balance-Principle

Principle = Monthly payment - Interest

Interest = Beginning Month balance*Monthly interest rate.

In $ Beginning Balance End of Month Balance Principle Interest 1 430421.54 395764.81 34656.73 19199.27 2 395764.81 359468.92 36295.89 17560.11 3 359468.92 321456.40 38012.52 15843.5 4 321456.40 281646.07 39810.33 14045.7 5 281646.07 239952.90 41693.17 12162.8 6 239952.90 196287.84 43665.06 10190.9 7 196287.84 150557.63 45730.21 8125.79 8 150557.63 102664.60 47893.03 5962.97 9 102664.60 52506.46 50158.15 3697.85 10 52506.46 -23.93 52530.39 1325.61

Related Questions

Navigate

• Browse (All)
• Browse P
• Subjects

---

---

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