Please give excel function!

SOLVE FOR: Original Basis,

Effective Gross Income (EGI) (Yr 7),

Loan balance end of year 2,

Net Operating Income (NOI) Yr 7,

After Tax Cash Flow YR 7,

Forecasted Sales Price YR 7,

Net Proceeds Upon Sale of Property YR 7 (assume 1031 exchange or no tax),

Net Proceeds Upon Sale of Property (after tax).,

Internal Rate of Return (IRR),

Breakeven Occupancy Rate YR7,

An individual is invested in a property. The NOI = $100, Before Tax Cash Flow (after debt service) = $40, Tax benefit (cash benefit this year) = $20, Appreciation of 3% of the $1,000 building, and principal reduction of $5. How much cash did the investor "put in his pocket" this year from this investment?

\begin{tabular}{|l|r|} \hline Descriptive Statistics & \\ \hline Name & Tulip Apts \\ \hline Analysis Begin Date & XIX/XXXX \\ \hline Address & 1234 Elm Street \\ \hline City & Anywhere \\ \hline State & USA \\ \hline Total Units & 100 \\ \hline Unit Size (sq. ft.) & 1,000 \\ \hline Holding Period (Years) & 7 \\ \hline Lease Term (Yrs) & 1 \\ \hline List Price & \\ \hline Purchase Price (or Offer Price) & S \\ \hline \end{tabular} \begin{tabular}{|c|c|c|} \hline \multicolumn{3}{|l|}{ Mortgage Related } \\ \hline Loan-to-Value: (Loan to Cost including immediate upgrades) & & 80.00% \\ \hline Mortgage Points & & 1.00% \\ \hline Other fees/costs for obtaining a mortgage & s & \\ \hline \multicolumn{3}{|l|}{ Additional Acquisition Costs } \\ \hline Legal, Closing, \& Due Diligence & $ & 13,000 \\ \hline Immediate Upgrades/Repairs & s & 50,000 \\ \hline Broker Fees & $ & - \\ \hline Security Deposits (-) & $ & - \\ \hline Insurance (1st year) & s & 30,000 \\ \hline \end{tabular} \begin{tabular}{|c|c|c|c|c|c|} \hline \multicolumn{6}{|l|}{ Income and Expense Assumptions: Rates and Growth } \\ \hline Year & 1 & 2 & 3 & 4 & 5 \\ \hline Units & 100 & 100 & 100 & 100 & 100 \\ \hline Average Rent Per Unit & $1,100.00 & & & & \\ \hline Laundry Income/unit/year & so & & & & \\ \hline Miscellaneous Rent Charges/unit/year & $50 & & & & \\ \hline Market Rent/Annual income increase & N/A & 3.0% & 3.0% & 3.0% & 3.0% \\ \hline Annual Laundry Income increase/Misc. Rent Charge Increase & NA & 3.0% & 3.0% & 3.0% & 3.0% \\ \hline Annual expense increase & N/A & 3.0% & 3.0% & 3.0% & 3.0% \\ \hline Vacancy Rate & 3.0% & 3.0% & 3.0% & 3.0% & 3.0% \\ \hline Credit Loss Rate & 1.0% & 1.0% & 1.0% & 1.0% & 1.0% \\ \hline Rental Concessions & 0.0% & 0.0% & 0.0% & 0.0% & 0.0% \\ \hline Reserves / Unit / Year & $500 & & & & \\ \hline Annual Asset Management Fee & 5.00% & 5.00% & 5.00% & 5.00% & 5.00% \\ \hline Buffer as a \%: Contingent Expenses as a total of variable expenses & 5.00% & 5.00% & 5.00% & 5.00% & 5.00% \\ \hline \multicolumn{6}{|l|}{ Note on buffer: As a percentage 5% is a common figure. } \\ \hline \multicolumn{6}{|l|}{ Turnover Figures } \\ \hline \# Years Property 100% Turns Over & 10 & & & & \\ \hline \begin{tabular}{|l} Upgrades to Units not budgeted in "Reserves" - Per Unit Cost \\ \end{tabular} & & & & & \\ \hline \end{tabular} \begin{tabular}{|l|r|} \hline Mortgage Terms & \\ \hline Interest Rate (Annual Nominal Rate) & 5.00% \\ \hline Amortization Period (years): & 30 \\ \hline \end{tabular} \begin{tabular}{|l|r|} \hline Income Tax Considerations & 27.5 \\ \hline Years for Cost Recovery (depreciation) & 25.0% \\ \hline Investor's Income Tax Bracket & 20.0% \\ \hline Tax Rate on Cost Recovery & 15.0% \\ \hline Capital Gains Tax Rate & Yes \\ \hline Investor Active Real Estate Professional? & \\ \hline \end{tabular} \begin{tabular}{|c|c|c|} \hline \multicolumn{3}{|l|}{ Income Tax Considerations: Allocation } \\ \hline Land Allocation & 20.00% & \\ \hline Building Allocation & 80.00% & \\ \hline Original Basis & & \\ \hline \end{tabular} \begin{tabular}{|c|c|} \hline Re-Sale of Property & \\ \hline Capitalization Rate To Establish Value: & 7.50% \\ \hline Inflationary Increase in Cash Flows, following year of sale & 3.00% \\ \hline Closing Costs (\% of Sales Price) & 3.00% \\ \hline Year & \\ \hline Expenses: & \\ \hline Real Estate/Property Taxes & 80,000 \\ \hline Utilities & 30,000 \\ \hline Variable Expenses & \\ \hline Personnel & \\ \hline On-site Management & 50,000 \\ \hline Maintenance & 40,000 \\ \hline Cleaning & 10,000 \\ \hline Other Payroll Expense & 2,000 \\ \hline Office Expense & 6,000 \\ \hline Advertising/Marketing & 12,000 \\ \hline Outside Contractors & 15,000 \\ \hline Building Services & 9,000 \\ \hline Maintenance Supplies & 17,000 \\ \hline Grounds & 10,000 \\ \hline Other & 0 \\ \hline \end{tabular} Cost Recovery (Depreciation) \begin{tabular}{|l|r|} \hline Descriptive Statistics & \\ \hline Name & Tulip Apts \\ \hline Analysis Begin Date & XIX/XXXX \\ \hline Address & 1234 Elm Street \\ \hline City & Anywhere \\ \hline State & USA \\ \hline Total Units & 100 \\ \hline Unit Size (sq. ft.) & 1,000 \\ \hline Holding Period (Years) & 7 \\ \hline Lease Term (Yrs) & 1 \\ \hline List Price & \\ \hline Purchase Price (or Offer Price) & S \\ \hline \end{tabular} \begin{tabular}{|c|c|c|} \hline \multicolumn{3}{|l|}{ Mortgage Related } \\ \hline Loan-to-Value: (Loan to Cost including immediate upgrades) & & 80.00% \\ \hline Mortgage Points & & 1.00% \\ \hline Other fees/costs for obtaining a mortgage & s & \\ \hline \multicolumn{3}{|l|}{ Additional Acquisition Costs } \\ \hline Legal, Closing, \& Due Diligence & $ & 13,000 \\ \hline Immediate Upgrades/Repairs & s & 50,000 \\ \hline Broker Fees & $ & - \\ \hline Security Deposits (-) & $ & - \\ \hline Insurance (1st year) & s & 30,000 \\ \hline \end{tabular} \begin{tabular}{|c|c|c|c|c|c|} \hline \multicolumn{6}{|l|}{ Income and Expense Assumptions: Rates and Growth } \\ \hline Year & 1 & 2 & 3 & 4 & 5 \\ \hline Units & 100 & 100 & 100 & 100 & 100 \\ \hline Average Rent Per Unit & $1,100.00 & & & & \\ \hline Laundry Income/unit/year & so & & & & \\ \hline Miscellaneous Rent Charges/unit/year & $50 & & & & \\ \hline Market Rent/Annual income increase & N/A & 3.0% & 3.0% & 3.0% & 3.0% \\ \hline Annual Laundry Income increase/Misc. Rent Charge Increase & NA & 3.0% & 3.0% & 3.0% & 3.0% \\ \hline Annual expense increase & N/A & 3.0% & 3.0% & 3.0% & 3.0% \\ \hline Vacancy Rate & 3.0% & 3.0% & 3.0% & 3.0% & 3.0% \\ \hline Credit Loss Rate & 1.0% & 1.0% & 1.0% & 1.0% & 1.0% \\ \hline Rental Concessions & 0.0% & 0.0% & 0.0% & 0.0% & 0.0% \\ \hline Reserves / Unit / Year & $500 & & & & \\ \hline Annual Asset Management Fee & 5.00% & 5.00% & 5.00% & 5.00% & 5.00% \\ \hline Buffer as a \%: Contingent Expenses as a total of variable expenses & 5.00% & 5.00% & 5.00% & 5.00% & 5.00% \\ \hline \multicolumn{6}{|l|}{ Note on buffer: As a percentage 5% is a common figure. } \\ \hline \multicolumn{6}{|l|}{ Turnover Figures } \\ \hline \# Years Property 100% Turns Over & 10 & & & & \\ \hline \begin{tabular}{|l} Upgrades to Units not budgeted in "Reserves" - Per Unit Cost \\ \end{tabular} & & & & & \\ \hline \end{tabular} \begin{tabular}{|l|r|} \hline Mortgage Terms & \\ \hline Interest Rate (Annual Nominal Rate) & 5.00% \\ \hline Amortization Period (years): & 30 \\ \hline \end{tabular} \begin{tabular}{|l|r|} \hline Income Tax Considerations & 27.5 \\ \hline Years for Cost Recovery (depreciation) & 25.0% \\ \hline Investor's Income Tax Bracket & 20.0% \\ \hline Tax Rate on Cost Recovery & 15.0% \\ \hline Capital Gains Tax Rate & Yes \\ \hline Investor Active Real Estate Professional? & \\ \hline \end{tabular} \begin{tabular}{|c|c|c|} \hline \multicolumn{3}{|l|}{ Income Tax Considerations: Allocation } \\ \hline Land Allocation & 20.00% & \\ \hline Building Allocation & 80.00% & \\ \hline Original Basis & & \\ \hline \end{tabular} \begin{tabular}{|c|c|} \hline Re-Sale of Property & \\ \hline Capitalization Rate To Establish Value: & 7.50% \\ \hline Inflationary Increase in Cash Flows, following year of sale & 3.00% \\ \hline Closing Costs (\% of Sales Price) & 3.00% \\ \hline Year & \\ \hline Expenses: & \\ \hline Real Estate/Property Taxes & 80,000 \\ \hline Utilities & 30,000 \\ \hline Variable Expenses & \\ \hline Personnel & \\ \hline On-site Management & 50,000 \\ \hline Maintenance & 40,000 \\ \hline Cleaning & 10,000 \\ \hline Other Payroll Expense & 2,000 \\ \hline Office Expense & 6,000 \\ \hline Advertising/Marketing & 12,000 \\ \hline Outside Contractors & 15,000 \\ \hline Building Services & 9,000 \\ \hline Maintenance Supplies & 17,000 \\ \hline Grounds & 10,000 \\ \hline Other & 0 \\ \hline \end{tabular} Cost Recovery (Depreciation)