FIN 435 – Fall 2021
Your starting point will be Part 1 of the Mini Case project and you will build on that. Many elements of Part 1 can be carried to this one, including proforma information, some formulas, etc. In Part 2, you will use the rent and expense information from the proforma statement you created in Part 1. In other words, rent, overage rent, and operating expenses will be the same as the ones you calculated in Part 1. Vacancy assumption, construction cost, land price, tax rates, and depreciation calculation will also remain the same. Loan amount and debt service will, however, be different from Part 1, as described below.
Create a drop-down box that will allow you to select between either a 70% LTV or an 80% LTV. Create a second drop-down box to select either a mortgage participation or no participation option with the lender based on the below information.
Given different combinations (four different combinations) with the drop-down boxes, the interest rate will change accordingly and will be as follows:
· LTV of 70% without Participation – Interest Rate of 5%
· LTV of 80% without Participation – Interest Rate of 6%
· LTV of 70% with Participation – Interest Rate of 3%
· LTV of 80% with Participation – Interest Rate of 4%
To program a cell to display the interest rate given the above parameters, you will need to create an IF statement combined with an AND statement, as there are two conditions (LTV and Participation) that will determine the interest rate. If you have never used or combined these formulas, try Googling some examples that could illustrate their combined application. I will also be happy to show you how to set them up.
The loan will be amortized over 20 years.
If participation is selected, the lender will receive 10% of the NOI each year.
You will need to set up an IF statement for the participation calculation.
Based on the above, complete the below tasks:
1. Calculate the IRR on the equity portion (BTIRR).
2. Calculate the after-tax IRR (ATIRR).
It will be interesting to see how the IRR metrics change as you select different parameters in the drop-down boxes.
Part 1 and Part 2 should be included in the same Excel file, but on different sheets that will be labeled Part 1 and Part 2.