In: Accounting
Given the following information provide an analysis that answers the questions at the end of the assignment.
Purchase Price: $4,250,000
Rents:
Tenant |
Unit 1 |
Unit 2 |
Unit 3 |
Unit 4 |
Unit 5 |
Unit 6 |
Unit 7 |
Unit 8 |
Monthly Rent |
$5,400 |
$5,400 |
$6,200 |
$3,500 |
$3,600 |
$3,300 |
$3,700 |
$3,100 |
Year 1 NOI: $248,678
NOI Growth Rate: 2.6%
Loan:
LTV: 75%
Amortization Period: 25 years, Term: 10 years
Interest Rate: 3.35%
Lender Points: 2%
Selling Costs: 3%
Going Out Cap Rate: 5.5%
Discount Rate: 10%
Questions:
Can someone teach me how to do this on excel or at least write out the formulas for me to be able to apply it to excel? thank you so much
Please see below formulas for the questions you need help in formula:
DSCR = Net Operating Income Divided by Total Debt service
(Net Operating Income is the Profit before Tax and Interest payments and Total Debt service is the amount of Interest obligtions on Debt)
Loan Balance at Sale = Loan Amount- Principal repayment
Net Present Value for this project= NPV (Discount rate,cells with cash flow value)
In Cash flow cells, Select each cashh flow value and separate it by comma (,)
IRR = IRR (Sum of Initial outflow and future cash flows)
In Value part give reference to sum of Outflow(Negative amount) and Inflow (Positive Amount)
DCF of the Project : XNPV (Rate,Values,Dates)
Rate : Discount rate, Values: Array of Cash Flow values, Dates: Array of Dates corresponding to each cash flow value.
Reversion Amount = Future Sale Price of the Asset- Balance Loan -Selling expense
Balance loan- Loan balance- Principal repayment, Selling expense- Cost to sale the asset