SHOW STEP BY STEP EXCEL CALCULATION FOR REAL ESTATE MATH HERE WITH ANSWERING ALL PHASE $2$: Phase $2$: Mortgage Decision$-$Making $$ Model rental income and expense cash flows under each mortgage option. $30$ Year Fixed Rate Mortgage $-$ Rental Income Mortgage Payment $=$ $$6,270\mathrm{.}66$ Operating Expenses $($~$40\%$ of gross income$)=$ $$6,270\mathrm{.}66/0\mathrm{.}6=$ $$10,451$ To generate a positive cash flow $(1$k$/$month$)$ $$6,270\mathrm{.}66+1,000=$ $$7,270\mathrm{.}66/0\mathrm{.}6=$ $$12,118$ This number is way too unrealistic. Average rent in the Fort Collins area for a house around ~$$1\mathrm{.}2$ million would sit around $4500-5000,$ so this tells us that a positive cash flow is not in the realm of possibilities here, at least not for a while.$$ A more realistic breakdown: Rent $=$ $$4,500$ Account for vacancy $($~$5\%)=-$$$225$ Effective Gross Income $=$ $$4,275$ Less $-$ Operating Expenses $(40\%$ of EGI$)=-$$$1,710$ EGI $-$ Operating Expenses $=$ NOI $=$ $$2,565$ Less $-$ Mortgage Payment $=-$ $$6,270\mathrm{.}66$ Cash Flow Before Taxes $=-$$$3,706$ Analyze return metrics: Cash$-$on$-$Cash Return, IRR, NPV$.$ Cash On Cash Return: $30$ yr loan: With an assumed $20\%$ down payment of $$239,000$ plus closing cost which we assume is $$59,750$ the total is $$298,750$ we can say that is what is used for our cash invested. Then the property being in Fort Collins we can assume that it will be rented for around $4000$ dollars a month. To get a positive leverage a rent of $12,118$ a month is needed equally $$145,416$ a year in potential rental income. This compares to our own mortgage payments of $$7,188\mathrm{.}66$ per month equals $$86,263\mathrm{.}92.$ After calculating it all together we know we will get a $19\%$ cash on cash return.$$ Net cash flow $=145,416-86,263\mathrm{.}92/$ Money invested $=298,750=\mathrm{.}1915$ yr loan: With a $15$ yr loan we will have the same down payment and closing cost of $$239,000$ and $$59,750$ for the same total of $$298,750.$ We can also assume that it will be rented for the same with a potential income of $$145,416.$ Our mortgage payments will be adjusted to $$9,436$ a month equaling $$113,232$ a year. This will lower our cash on cash to $10\%.$ Net cash flow $=145,416-113,232/$ Money invested $=298,750=\mathrm{.}10.$ Perform scenario testing for interest rate and property value changes $**$ don$$t need excel here, just compare the scenarios we$$ve discussed. For the realistic market rate $/$ negative cash flow section we can grab that rate and compare what a rise or fall in current interest rate would do to our scenarios Deliverables: $$ Financial model in Excel $$ Risk$-$return analysis $$ Strategic recommendation memo