Assignment: Build an Excel sheet that will compute flexibly the monthly amortization payments, monthly interest amounts,...

Assignment: Build an Excel sheet that will compute flexibly the monthly amortization payments, monthly interest amounts, monthly amortization principal amounts, and any other computationally relevant variables you may deem important. The Excel file must be driven by the pertinent formulae that define the typical Canadian mortgage contracts. We will examine your programming skills in Excel in this particular assignment.

Here are following mortgage facts:

Mortgage amount: Flexible and any amount the borrower wishes - i.e., your choice

Payment frequency: Monthly

Initial mortgage contract rate: flexible rate - i.e., your choice

Maturity: 20 years.

Rollover period: 2 years.

The first new contract rate: 2% higher than the initial rate at the end of the first rollover period

The second new contract rate: 3% lower than at the first new contract rate at the end of the second rollover period. Once contracted, this rate remains constant until the end of 20 years.

Expert Solution

Assumptions -

Mortgage amount - 100,000

Initial mortgage contract rate - 12%

After 22 years, rate - 14%

After 24 years, rate - 11%

Year	Amount	Rate	Monthly Payments	Total Yearly Payment
1	1,00,000	12	1000	12000
2	1,00,000	12	1000	12000
3	1,00,000	12	1000	12000
4	1,00,000	12	1000	12000
5	1,00,000	12	1000	12000
6	1,00,000	12	1000	12000
7	1,00,000	12	1000	12000
8	1,00,000	12	1000	12000
9	1,00,000	12	1000	12000
10	1,00,000	12	1000	12000
11	1,00,000	12	1000	12000
12	1,00,000	12	1000	12000
13	1,00,000	12	1000	12000
14	1,00,000	12	1000	12000
15	1,00,000	12	1000	12000
16	1,00,000	12	1000	12000
17	1,00,000	12	1000	12000
18	1,00,000	12	1000	12000
19	1,00,000	12	1000	12000
20	1,00,000	12	1000	12000
21	1,00,000	12	1000	12000
22	1,00,000	12	1000	12000
23	1,00,000	14	1166.67	14000
24	1,00,000	14	1166.67	14000
25	1,00,000	11	916.67	11000
26	1,00,000	11	916.67	11000

jeff jeffy answered 1 year ago

Prepare the amortization schedule for a thirty-year variable interest loan with monthly payments of $250,000 at...

Prepare the amortization schedule for a thirty-year variable interest loan with monthly payments of $250,000 at an APR of 6.8%.

a. Prepare the amortization schedule for a thirty-year variable interest loan with monthly payments of $250,000...

a. Prepare the amortization schedule for a thirty-year variable interest loan with monthly payments of $250,000 at an APR of 6.8%. specifies monthly compounding. b.What is the interest payment and principal amounts in the 110th payment? c. Use the annuity formula to find how much principal you still owe to the bank for the 110th payment. Check that this value is the same you have in your amortization schedule. d. How much in total interest will you pay? e. Suppose...

Barbara borrows $3000. She agrees to make monthly interest payments on the loan and will build...

Barbara borrows $3000. She agrees to make monthly interest payments on the loan and will build up a sinking fund with monthly deposits to repay the principal with a single payment 19 months from now. If the interest being charged on the loan is j12 = 8.5% and the interest being earned on the sinking fund is j12= 5.4%, what is the monthly cost of the debt for Barbara?

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?

PLEASE COMPUTE THE FOLLOWING IN EXCEL and show the excel sheet, Thank you so much! The...

PLEASE COMPUTE THE FOLLOWING IN EXCEL and show the excel sheet, Thank you so much! The following are the runs scored totals for 9 players for the 2016 New York Yankees: 56,43,63,68,58,80,71,32,19 (a) Find the mean and median (b) Find the standard deviation of this population (c) Considering this as a normal sample of American League players for the 2016 season, find a 99% Confidence Interval for the actual mean of Runs Scored for AL players, 2016 . (d) Considering...

Consider a $5,000,000, 9%, 25-year mortgage with monthly payments. Compute the first three payments and the...

Consider a $5,000,000, 9%, 25-year mortgage with monthly payments. Compute the first three payments and the loan balance after the third payment for each of the following loan types: (a) Interest-only, (b) CAM, (c) CPM.

A full amortization schedule for a note that is payed monthly (Interest and principle). The time...

A full amortization schedule for a note that is payed monthly (Interest and principle). The time frame is 5 years=60 months, the Interest Rate=5%, the Note is for $75,000.

Prepare an amortization spreadsheet in Excel. The sheet should be labeled, and I should be able to...

Prepare an amortization spreadsheet in Excel. The sheet should be labeled, and I should be able to change purchase price, interest rate, or the other relevant factors and the spreadsheet should automatically update. As we discussed in class, spend some time labeling the spreadsheet and using proper cell references. This is the first but not the last spreadsheet of this type, it is highly likely that elements of this spreadsheet will be helpful in subsequent assignments, so time spent here may mean...

Assume the effective interest method in accounting for these bonds. Build a 10 year amortization table....

Assume the effective interest method in accounting for these bonds. Build a 10 year amortization table. Bond $650,000 Mature 10 years State annual interest 9.25% Effective annual rate 6% Interest is paid every 6 months

On the first tab build the full amortization table for a 30 year Interest Only (IO)...

On the first tab build the full amortization table for a 30 year Interest Only (IO) Loan with a 5.5% interest rate compounded monthly. The initial loan amount should be $5,000,000. In Excel

Question