Question

You take out a 25-year mortgage for $300,000 to buy a new house. What will your...

You take out a 25-year mortgage for $300,000 to buy a new house. What will your monthly payments be if the interest rate on your mortgage is 8 percent? Now, calculate the portion of the 48th monthly payment that goes toward interest and principal.

Complete the steps below using cell references to given data or previous calculations. In some cases, a simple cell reference is all you need. To copy/paste a formula across a row or down a column, an absolute cell reference or a mixed cell reference may be preferred. If a specific Excel function is to be used, the directions will specify the use of that function. Do not type in numerical data into a cell or function. Instead, make a reference to the cell in which the data is found. Make your computations only in the green cells highlighted below. In all cases, unless otherwise directed, use the earliest appearance of the data in your formulas, usually the Given Data section.

Given Data:
Loan amount $300,000.00
Rate of interest 8.00%
Number of years 25
Month number for portion of monthly payment 48
Months in a year 12
Number of periods
Monthly interest rate
Payment
Interest amount in the 48th payment
Principal amount in the 48th payment
Requirements
1 Start Excel.
2 In cell D12, by using cell references, calculate the number of periods. (1 pt.)
3 In cell D13, by using cell references, calculate the monthly interest rate. (1 pt.)
4 In cell D14, by using cell references and the Excel PMT function, calculate the amount of monthly payments. Assume that all payments are made at the end of the period.

Note: The output of the expression or function you typed in this cell is expected as a positive number. (1 pt.)
5 In cell D15, by using cell references and the Excel IPMT function, calculate the portion of the 48th monthly payment that goes toward interest. Assume that the payment is made at the end of the period.

Note: The output of the expression or function you typed in this cell is expected as a positive number. (1 pt.)
6 In cell D16, by using cell references, calculate the portion of the 48th monthly payment that goes toward principal. (1 pt.)
7 Save the workbook. Close the workbook and then exit Excel. Submit the workbook as directed.

Homework Answers

Answer #1

It is an amortisation loan. We can find various fields in excel by using the excel functions as given below:

Know the answer?
Your Answer:

Post as a guest

Your Name:

What's your source?

Earn Coins

Coins can be redeemed for fabulous gifts.

Not the answer you're looking for?
Ask your own homework help question
Similar Questions
You have just sold your house for $1,000,000 in cash. Your mortgage was originally a 30-year...
You have just sold your house for $1,000,000 in cash. Your mortgage was originally a 30-year mortgage with monthly payments and an initial balance of $800,000. The mortgage is currently exactly 18½ years old, and you have just made a payment. If the interest rate on the mortgage is 5.25% (APR), how much cash will you have from the sale once you pay off the mortgage? Complete the steps below using cell references to given data or previous calculations. In...
​(Components of an annuity​ payment) You take out a 25​-year mortgage for ​$280 comma 000 to...
​(Components of an annuity​ payment) You take out a 25​-year mortgage for ​$280 comma 000 to buy a new house. What will your monthly payments be if the interest rate on your mortgage is 7 ​percent? Use a spreadsheet to calculate your answer.  ​Now, calculate the portion of the 108th monthly payment that goes toward interest and principal. Use five decimal places for the monthly interest rate in your calculations?
For a 30-year house mortgage of $300,000 at 4.8% interest, find the following. (Round your final...
For a 30-year house mortgage of $300,000 at 4.8% interest, find the following. (Round your final answers to two decimal places.) (a) What is the amount of the first monthly payment that goes to repay principal? (b) What is the amount of the 181st month's payment (after 15 years) that goes toward payment of principal?
you just took out a $500,000, 20-year mortgage from CIBC. ASSume your effective monthly rate of...
you just took out a $500,000, 20-year mortgage from CIBC. ASSume your effective monthly rate of interest is 0.5% and your monthly payments are $3582.16. (a) How much of your first monthly payment goes toward principal repayment? (b) how much of your 50th monthly payment goes toward interest?
You plan to purchase a $300,000 house using either a 30-year mortgage obtained from your local...
You plan to purchase a $300,000 house using either a 30-year mortgage obtained from your local savings bank with a rate of 7.80 percent, or a 20-year mortgage with a rate of 7.20 percent. You will make a down payment of 15 percent of the purchase price. a. Calculate the amount of interest and, separately, principal paid on each mortgage. What is the difference in interest paid? b. Calculate your monthly payments on the two mortgages. What is the difference...
You take out standard 30-year mortgage with fixed monthly payments to purchase your house. The mortgage...
You take out standard 30-year mortgage with fixed monthly payments to purchase your house. The mortgage is for $250,000 with a nominal annual rate of 4.6% (Monthly compounding). Each month, you send in a check for $1,403.81, which is above the required payment, where the excess payment directly reduces the outstanding balance each month. What portion of your payments in months 25-36 go towards interest?
A. Julio has taken out a 25-year mortgage for $82,000.       The interest rate is 8.5%...
A. Julio has taken out a 25-year mortgage for $82,000.       The interest rate is 8.5% and the down payment is 20%.       Calculate the monthly payment. Purchase Price $82,000.00 Interest Rate 8.5% Length of Loan in Years 25 Down Payment Percent 20% Down Payment Amount Financed Units Factor 8.0528 Monthly Payment B. Complete the amortization schedule for the first three months of Julio’s mortgage. Month Monthly Payment Interest Portion Principal Portion Loan Balance ––––– ––––– ––––– ––––– 1 2...
Mandy would like to buy an apartment and needs a mortgage for $280,000. She was able...
Mandy would like to buy an apartment and needs a mortgage for $280,000. She was able to qualify for a loan at 7.2% for 30 years. What is the amount of her monthly payment? Use a spreadsheet program like Microsoft Excel. Start with a blank worksheet. In your spreadsheet, create a TVM DataFrame TVM DataFrame c n i PV PMT FV 1 From the problem, fill-in the values for Mandy's mortgage annuity into your spreadsheet. Place a question mark in...
You plan to purchase a $390,000 house using either a 30-year mortgage obtained from your local...
You plan to purchase a $390,000 house using either a 30-year mortgage obtained from your local savings bank with a rate of 8.50 percent, or a 15-year mortgage with a rate of 7.55 percent. You will make a down payment of 20 percent of the purchase price. a. Calculate the amount of interest and, separately, principal paid on each mortgage. What is the difference in interest paid? b. Calculate your monthly payments on the two mortgages. What is the difference...
You take out a $325,000 thirty-year mortgage (amortized loan) when you purchase your home. The interest...
You take out a $325,000 thirty-year mortgage (amortized loan) when you purchase your home. The interest rate is 6%. You make monthly payments of $1948.54. What is the principal portion of your first payment?