[SHORT] In Excel: Amount of the loan: $500,000 Length of the loan: 30 years Payment: Equal...

[SHORT] In Excel:

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 at 0.1% increment every year after that. So Year 2's rate will be 3.1%, Year 3's rate will be 3.2%, etc.

Create a year-by-year table with Year, Beginning balance, PMT, interest, principal, and ending balance.

Make sure to create a column to reflect floating interest rates.

Make sure to utilize goal seek to calculate loan annual payment.

Expert Solution

By using GOALSEEK method.

jeff jeffy answered 3 months ago

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...

Amortizing Loan Assignment Excels: Loan Amount Term (years) Interest Rate Payment Frequency Punam Barua $500,000 25...

Amortizing Loan Assignment Excels: Loan Amount Term (years) Interest Rate Payment Frequency Punam Barua $500,000 25 3.50% monthly Militza Bodesinsky $9,500 3 25.00% monthly Rob Boucher $350,000 20 3.00% monthly Janice Coleman $1,000,000 15 2.50% monthly Sadiera Crawford $6,500 3 30.50% monthly Delitzel Cruz $750,000 20 4.00% monthly Eroldy Duverge $25,000 5 6.25% monthly Arlene Flores-Icaza $400,000 30 5.25% monthly John Gurcak $35,000 6 6.75% monthly George Juzdan $800,000 30 3.65% monthly Amani Katerji $10,000 3 30.00% monthly Shanay Leary...

The maturity of the balloon loan is 5 years, amortization period is 30 years, loan amount...

The maturity of the balloon loan is 5 years, amortization period is 30 years, loan amount is $85,000 with annual interest rate 4.5% and monthly payments. What is the monthly payment for this balloon mortgage? What is the balloon payment at the end of five years for this balloon mortgage?

A loan of $10,000 is amortized by equal annual payments for 30 years at an effective...

A loan of $10,000 is amortized by equal annual payments for 30 years at an effective annual interest rate of 5%. The income tax rate level is at 25%. Assume the tax on the interest earned is based on the amortization schedule. a) Determine the income tax in the 10th year b) Determine the total income taxes over the life of the loan c) Calculate the present value of the after-tax payments using the before-tax yield rate. Answer to the...

Loan Amortization Schedule, $80,000,000 at 8% for 15 years Year Beginning Amount Payment Interest Payment of...

Loan Amortization Schedule, $80,000,000 at 8% for 15 years Year Beginning Amount Payment Interest Payment of Principal Ending Balance 1 $ 80,000,000.00 $ 1,933.28 $ 533,333.33 $ (531,400.05) $ 80,531,400.05 2 $ 80,531,400.05 $ 1,933.28 $ 536,876.00 $ (534,942.72) $ 81,066,342.77 3 $ 81,066,342.77 $ 1,933.28 $ 540,442.29 $ (538,509.01) $ 81,604,851.78 4 $ 81,604,851.78 $ 1,933.28 $ 544,032.35 $ (542,099.07) $ 82,146,950.84 179 $ 260,404,969.06 $ 1,933.28 $ 1,736,033.13 $ (1,734,099.85) $ 262,139,068.91 180 $ 262,139,068.91 $ 1,933.28 $ ...

Calculate the monthly payment for loan amount of $250,000 using the following parameters: • 30 Year...

Calculate the monthly payment for loan amount of $250,000 using the following parameters: • 30 Year Loan, fully amortizing • Interest Rate of 3.50% What is the monthly payment amount (rounded to the nearest dollar)? a. $1,123 b. $1,321 c. $1,572 d. $1,252 Calculate the monthly payment for loan amount of $250,000 using the following parameters: • 15 Year Loan, fully amortizing • Interest Rate of 3.09% What is the monthly payment amount (rounded to the nearest dollar)? a. $1,398...

Sara is 30 years old today and wants to set aside an equal amount at the...

Sara is 30 years old today and wants to set aside an equal amount at the end of each of the next 30 years into a retirement fund earning 10% p.a. so that he can retire at age 60. At retirement , the funds will be transferred to an investment account earning 6% p.a. He expects to live to age 80 and wants to be able to withdraw $375,000 per year from the account on his 61st through 80th birthdays....

A 5-year, 10%, $500,000 loan requires equal principal payments in years 4 and 5 ($250,000 each...

A 5-year, 10%, $500,000 loan requires equal principal payments in years 4 and 5 ($250,000 each year). Show the cash flows (interest and principal payments) for years 1-5.

4. Calculate the new monthly payment given a $150,000 loan for 6%, 30 years (monthly)...

4. Calculate the new monthly payment given a $150,000 loan for 6%, 30 years (monthly) with a lump-sum pay down of $20,000 made after 12 years have lapsed. New MP: Calculate the upfront fees that needs to be charged by the lender given a loan for $150,000 with terms 6%, 30 years (monthly compounding). The lender wants to earn a 6.6% yield. Assume a holding period of 12 years. Calculate the total payments and total interest expense for a...

loan amount - 1,200,000. terms(years) - 15 interest rate - 3.125% payment frequency - monthly Amortizing...

loan amount - 1,200,000. terms(years) - 15 interest rate - 3.125% payment frequency - monthly Amortizing Loan Assignment

Question