Answered step by step
Verified Expert Solution
Link Copied!

Question

1 Approved Answer

(I NEED EXCEL FORMULAS) Write an excel spreadsheet to amortize a general ARM. Use the (monthly) index toy model (TBD in class): (initial rate +

(I NEED EXCEL FORMULAS) Write an excel spreadsheet to amortize a general ARM. Use the (monthly) index toy model (TBD in class): (initial rate + x* 0.04/12, where x= uniform random number between -1 and 1, and ensure that the rate is never negative, nor ever larger than 20%). Switch off the automatic re-computation feature in excel. Make sure that your program can handle. 1. Different interest rate caps (periodic, lifetime and initial rate increase. This also means your program will be able to handle fixed rate mortgages) [1/4/2]; 2. Payment caps (with neg am cap; assume once this is hit, assume that the loan becomes amortizing according to 5 below) [10%, 120%]; 3. Different reset periods. While the index should be recomputed monthly, the reset should be specified in years (see also 6 below) [1]; 4. Different mortgage terms (up to 360 months) [180]; 5. Different amortization terms [180]; 6. Differing hybrid types (1/1, 3/1, 5/1, etc.), along with the rate for the fixed interest rate periods [3/1, 3.5%]; 7. Differing initial index rate [3%]. The inputs above (along with the margin [2.5%] and loan amount [100k]) must be taken from a separate input sheet. That is, if I change any of the terms above in one input cell on the input tab, the amortization schedule (CF sheet) should correctly re-compute when cntrl+= is pressed without having to alter anything in the CF sheet.

Step by Step Solution

There are 3 Steps involved in it

Step: 1

blur-text-image

Get Instant Access to Expert-Tailored Solutions

See step-by-step solutions with expert insights and AI powered tools for academic success

Step: 2

blur-text-image

Step: 3

blur-text-image

Ace Your Homework with AI

Get the answers you need in no time with our AI-driven, step-by-step assistance

Get Started

Recommended Textbook for

More Books

Students also viewed these Accounting questions

Question

Design a health and safety policy.

Answered: 1 week ago