Set up an amortization schedule that amortizes $500,000 with 5 years of cyclical quarterly payments. The...

Set up an amortization schedule that amortizes $500,000 with 5 years of cyclical quarterly payments. The annual interest rate is 6%. The payment schedule is: " one-third a normal payment" at the end of quarter 1, "normal payments" at the end of quarters 2 and 3, and "50% of a normal payment" at the end of quarter 4.

please solve in excel and list equations that go in the cells. Thank you :)

Solutions

Expert Solution

Normal payment is calculated using PMT function in Excel :

rate = 6% / 4 (converting annual rate into quarterly rate)

nper = 5 * 4 (5 years with 4 quarterly payments each year)

pv = -500000 (beginning principal)

PMT is calculated to be $29,122.87

Now, the amortization schedule looks as below :

Now, we use goal seek to change the value of "normal payment" so that the principal outstanding at the end of 5 years is zero, i.e. the loan is fully amortized

"Normal payment is calculated to be $41,170.60

jeff jeffy answered 3 weeks ago

Next > < Previous

Related Solutions

Set up an amortization schedule that amortizes $500,000 with 5 years of cyclical quarterly payments. The...

Set up an amortization schedule that amortizes $500,000 with 5 years of cyclical quarterly payments. The annual interest rate is 6%. The Payment Schedule is:one-third normal payments at the end of quarter 1, normal payments at the end of quarters 2 and 3, and 50% of a normal payment at the end of quarter 4

Amortization schedule Set up an amortization schedule for a $36,000 loan to be repaid in equal...

Amortization schedule Set up an amortization schedule for a $36,000 loan to be repaid in equal installments at the end of each of the next 3 years. The interest rate is 6% compounded annually. Round all answers to the nearest cent. Beginning Remaining Year Balance Payment Balance 1 $ $ $ 2 $ $ $ 3 $ $ $ What percentage of the payment represents interest and what percentage represents principal for each of the 3 years? Round all answers...

Set up an amortization schedule a $30,000 loan to be repaid in equal installments at the...

Set up an amortization schedule a $30,000 loan to be repaid in equal installments at the next 3 years. The interest rate is 8% compounded semiannually a) amortize loan with fixed principal payment b) amortized loan with fixed payment

A. Set up an amortization schedule for a $60,000 loan to be repaid in 20 equal...

A. Set up an amortization schedule for a $60,000 loan to be repaid in 20 equal annual installments at an interest rate of 20 percent. What is the annual payment? B. Using the model that has worked for the first problem, figure out the annual payments for a 20-year mortgage on a home where a buyer finances 90% of the home’s value (e.g., they pay 10% as a down payment) for a $185,000 home at an interest rate of 3.5%....

Set up an amortization schedule for a $30,000 loan to be repaid in equal instalments at...

Set up an amortization schedule for a $30,000 loan to be repaid in equal instalments at the end of each of the next 3 years. The interest rate is 3 percent, compounded annually. A. What percentage of the payment represents interest payment and what percentage of the payment represents principal payment for each of the 3 years?B.Why do these percentages change over time?

Set up an amortization schedule for a $30,000 loan to be repaid in equal installments at...

Set up an amortization schedule for a $30,000 loan to be repaid in equal installments at the end of each of the next 20 years at an interest rate of 10 percent. What is the annual payment?

Implement an amortization schedule of a loan of at least 5 years in excel

Construct the amortization schedule for a $15000 debt that is to be amortized in 10 quarterly...

Construct the amortization schedule for a $15000 debt that is to be amortized in 10 quarterly payments at 3% interest per quarter on the unpaid balance.

Goal-seek Loan Amortization Schedule Amount of the loan: $500,000 Length of the loan: 30 years Payment:...

Goal-seek Loan Amortization Schedule Amount of the loan: $500,000 Length of the loan: 30 years Payment: Equal annual payment Interest rate: Annual interest rate is 3.0% in year 1, and increases 0.1% in year 2, etc. In other words, the annual interest rates will be 3.0%, 3.1%, 3.2%,…. M1 Assignment - Goal-seek Loan Amortization Schedule Rubric Successfully created year-by-year table with Year, Beginning balance, PMT, interest, principal, ending balance 3 pts Successfully created a column to reflect floating interest rates...

Amortization with Equal Payments. Prepare an amortization schedule for a three-year loan of $57,000. The interest...

Amortization with Equal Payments. Prepare an amortization schedule for a three-year loan of $57,000. The interest rate is 8 percent per year, and the loan calls for equal annual payments. How much interest is paid in the third year? How much total interest is paid over the life of the loan?

Subjects