Question

Create a worksheet that through inputs and a series of formulas, be a basis of an...

Create a worksheet that through inputs and a series of formulas, be a basis of an expandable tool, that you can use to create project an amortization schedule.

The spreadsheet should be able to do various calculations using automated formulas.

  • Fully amortizing, partially amortizing (balloon), partial interest only or Interest only (bullet).
  • there should be no balance or payment of interest past the term or error messages
  • Yield with or with out closing costs.
  • Interest Only periods.
  • should be fully formula driven
  • Able to change loan amount, amortization period, term length, interest rate.

Also include:

Calculate date of each payment

Calculate the sum of interest and principal for calendar or loan year

Actual vs 360 payments.

Average life

Duration

SHOW ALL EXCEL FORMULAS that are USED IN the EXCEL cell AND USE YOUR OWN NUMBERS THANKS

Homework Answers

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
Solve using excel: A. Given the following information, calculate the balloon payment for a partially amortized...
Solve using excel: A. Given the following information, calculate the balloon payment for a partially amortized mortgage. Loan amount: $84,000, Term to maturity: 7 years, Amortization Term: 30 years, Interest rate: 4.5%, Monthly Payment: $425.62. 
 B. Given the following information about a fully amortizing loan, calculate the lender's yield (rounded to the nearest tenth of a percent). Loan amount: $166,950, Term: 30 years, Interest rate: 8 %, Monthly Payment: $1,225.00, Discount points: 2. C. Given the following information, calculate the...
Show work through excel using formulas: but please actually show each step you use to do...
Show work through excel using formulas: but please actually show each step you use to do it on excel! 1. Assume you have taken out a balloon mortgage loan for $2,500,000 to finance the purchase of a commercial property. The loan has a term of 5 years, but amortizes over 25 years. Calculate the balloon payment at maturity (Year 5) if the interest rate on this loan is 4.5%. A. $5,637.99 B. $13, 895.82 C. $2,196,447.59 D. $2,495,479.19
Answer using excel: Please show how you do it   on excel with formulas! 1. Given the...
Answer using excel: Please show how you do it   on excel with formulas! 1. Given the following information, calculate the effective borrowing cost (rounded to the nearest tenth of a percent). Loan amount: $166,950, Term: 30 years, Interest rate: 8 %, Monthly Payment: $1,225.00, Discount points: 2, Other Closing Expenses: $3,611. 
 A. 7.7% B. 8.2% C. 8.5% D. 9.1% 2. Suppose a potential home buyer is interested in taking a $500,000 mortgage loan that has a term of 30 years...
Division of Net Income Between Partners Create an Excel worksheet. At the TOP, enter all of...
Division of Net Income Between Partners Create an Excel worksheet. At the TOP, enter all of these amounts given: Ryan's salary: $80,000 Limoli's salary: $60,000 Ryan's beginning capital account: $250,000 Limoli's beginning capital account: $100,000 Interest on beginning capital accounts: 8% Net income for the year: $150,000 Remainder of net income to be shared 60% to Ryan, 40% to Limoli Enter each amount in a separate cell and LABEL EVERYTHING. Don't do any calculations for the top - just enter...
Amortization Schedule Project Program an Excel Spreadsheet to complete a Fixed Payment Loan Amortization Schedule based...
Amortization Schedule Project Program an Excel Spreadsheet to complete a Fixed Payment Loan Amortization Schedule based on the 5 inputs (see 1a below). This spreadsheet should work for any scenario chosen using the 5 inputs below that does not exceed 360 periods. You may start with any 5 inputs as these will be changed multiple times during grading. All cells with number format should have no decimal places. All cells with accounting format should have 2 decimal places. All cells...
Cajun Jack needs $45,000 for a boat used to hunt crocodiles that are used both for...
Cajun Jack needs $45,000 for a boat used to hunt crocodiles that are used both for their leather and meat. Cajun is considering taking a 9-year loan for the required amount and he talked to the lenders from his local Bank and Credit Union regarding loan options. The local bank charges 6.25% per year compounding quarterly and requires quarterly repayments. (a) Calculate the quarterly repayment for both principal and interest that Cajun would have to make on this loan. If...
Jimmy Herring can borrow $100,000 for three years at 8.0% interest with monthly payments based on...
Jimmy Herring can borrow $100,000 for three years at 8.0% interest with monthly payments based on a 30-year amortization. Assume twelve 30-day months in a year, payments always made on the first day of the month and monthly accrual of interest. Create an interactive amortization schedule in Excel that contains an input menu and that shows monthly payments, monthly interest, monthly principal and the ending monthly balance in columns. Beginning and ending dates do not matter at this point –...
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...
Create a dollar roll matrix of breakeven rates for an agency MBS with gross and deal...
Create a dollar roll matrix of breakeven rates for an agency MBS with gross and deal coupons of 8.035% and 7.5%, respectively, and settlement dates 6/14/18 and 8/15/18. Assume standard fully amortizing fixed rate mortgages with a term of 30:0, a WAM of 29:5 and an immediate price of 96-16. MBS CF’s are due the owner of record on the first of each month, but are paid on the 25th of that month. Assume standard PSA benchmarks. The spreadsheet should...
I want you to find a home that you would like to buy. Use Google, Zillow,...
I want you to find a home that you would like to buy. Use Google, Zillow, Realtor.com, or another real estate site to find a home. Make sure to include the link for the home in your discussion post. Next, prepare an Amortization Schedule for this home. I expect the amortization schedule to be completed in Excel with all work shown in Excel. Thus, I should be able to click on any cell and see your work embedded in that...