+1443 776-2705 panelessays@gmail.com

use the same excel sheet i have provided.

I need it within 2 hrs .

let know if you can do it 

Part 1

Base rent increases 0.03 Land Price $ 1,000,000.00 Comparables Price NOI Cap rate
Increase in sales 0.02 construction cost $ 22,000,000.00 Property A $ 28,750,000.00 $ 2,300,000.00 8.00%
SF Base Rent per SF (Annual) Overage Rent Breakpoint Sales total cost $ (23,000,000.00) $ 28,372,450.46 Property B $ 26,000,000.00 $ 1,950,000.00 7.50%
Fresh Foods 75,000.00 $ 12.00 $ 0.03 $ 1,500,000.00 $ 4,300,000.00 Loan amount $18,491,624.70 ($11,505,769.64) Property C $ 30,000,000.00 $ 2,100,000.00 7.00%
Great Electronics 55,000.00 $ 14.00 $ 0.03 $ 1,000,000.00 $ 4,100,000.00 Average Cap rate 7.50%
Fancy Shoes 45,000.00 $ 15.50 $ 0.03 $ 1,000,000.00 $ 3,700,000.00 2) Direct Capitalization Method NOI Value
10 In-Line Tenants 50,000.00 $ 18.00 N/A N/A $ 3,300,000.00 $ 1,903,770.00 $ 25,383,600.00
Total 225,000.00
DCR 1.30
Interest pa 0.05
Vacancy and collection losses 0.10
Operating expenses 0.40 3) Discounted Cash Flow Method
Depreciation years 39.00
Tax 0.35
10 Year-old Comps. Price NOI Cap Rate Req R Average Cap Rate
Property D $ 18,750,000.00 $ 1,500,000.00 8.0% 10% 9.0%
1) Pro pharma statement Property E $ 17,500,000.00 $ 1,750,000.00 10.0%
Base rent 1 2 3 4 5 6 7 8 9 10 11 Property F $ 20,000,000.00 $ 1,800,000.00 9.0%
Fresh Foods $ 900,000.00 $ 927,000.00 $ 954,810.00 $ 983,454.30 $ 1,012,957.93 $ 1,043,346.67 $ 1,074,647.07 $ 1,106,886.48 $ 1,140,093.07 $ 1,174,295.87 $ 1,209,524.74 Terminal Cap rate 9.0%
Great Electronics $ 770,000.00 $ 793,100.00 $ 816,893.00 $ 841,399.79 $ 866,641.78 $ 892,641.04 $ 919,420.27 $ 947,002.88 $ 975,412.96 $ 1,004,675.35 $ 1,034,815.61
Fancy Shoes $ 697,500.00 $ 718,425.00 $ 739,977.75 $ 762,177.08 $ 785,042.39 $ 808,593.67 $ 832,851.48 $ 857,837.02 $ 883,572.13 $ 910,079.30 $ 937,381.67
10 In-Line Tenants $ 900,000.00 $ 927,000.00 $ 954,810.00 $ 983,454.30 $ 1,012,957.93 $ 1,043,346.67 $ 1,074,647.07 $ 1,106,886.48 $ 1,140,093.07 $ 1,174,295.87 $ 1,209,524.74 NOI PV 10% Revenue 10% PV of Revenue Sum of PV
1 $ 1,903,770.00 $1,730,700.00 $1,730,700.00
Sales 1 2 3 4 5 6 7 8 9 10 11 2 $ 1,960,623.90 $1,620,350.33 $1,620,350.33
Fresh Foods $ 4,300,000.00 $ 4,386,000.00 $ 4,473,720.00 $ 4,563,194.40 $ 4,654,458.29 $ 4,747,547.45 $ 4,842,498.40 $ 4,939,348.37 $ 5,038,135.34 $ 5,138,898.05 $ 5,241,676.01 3 $ 2,019,144.21 $1,517,012.93 $1,517,012.93
Great Electronics $ 4,100,000.00 $ 4,182,000.00 $ 4,265,640.00 $ 4,350,952.80 $ 4,437,971.86 $ 4,526,731.29 $ 4,617,265.92 $ 4,709,611.24 $ 4,803,803.46 $ 4,899,879.53 $ 4,997,877.12 4 $ 2,079,380.15 $1,420,244.62 $1,420,244.62
Fancy Shoes $ 3,700,000.00 $ 3,774,000.00 $ 3,849,480.00 $ 3,926,469.60 $ 4,004,998.99 $ 4,085,098.97 $ 4,166,800.95 $ 4,250,136.97 $ 4,335,139.71 $ 4,421,842.50 $ 4,510,279.35 5 $ 2,141,382.37 $1,329,629.98 $1,329,629.98
10 In-Line Tenants $ 3,300,000.00 $ 3,366,000.00 $ 3,433,320.00 $ 3,501,986.40 $ 3,572,026.13 $ 3,643,466.65 $ 3,716,335.98 $ 3,790,662.70 $ 3,866,475.96 $ 3,943,805.48 $ 4,022,681.59 6 $ 2,205,203.06 $1,244,779.64 $1,244,779.64
7 $ 2,270,895.93 $1,165,328.68 $1,165,328.68
8 $ 2,338,516.31 $1,090,935.12 $1,090,935.12
1 2 3 4 5 6 7 8 9 10 11 9 $ 2,408,121.14 $1,021,278.44 $1,021,278.44
Sales revenue 10 $ 2,479,769.09 $956,058.33 $ 28,372,450.46 $10,938,807.88 $11,894,866.21
Fresh foods $ 984,000.00 $ 1,013,580.00 $ 1,044,021.60 $ 1,075,350.13 $ 1,107,591.68 $ 1,140,773.09 $ 1,174,922.02 $ 1,210,066.93 $ 1,246,237.13 $ 1,283,462.81 $ 1,321,775.02 11 $ 2,553,520.54 $894,993.37
Great Electronics $ 863,000.00 $ 888,560.00 $ 914,862.20 $ 941,928.37 $ 969,780.94 $ 998,442.98 $ 1,027,938.25 $ 1,058,291.21 $ 1,089,527.07 $ 1,121,671.74 $ 1,154,751.93
Fancy Shoes $ 778,500.00 $ 801,645.00 $ 825,462.15 $ 849,971.17 $ 875,192.36 $ 901,146.64 $ 927,855.51 $ 955,341.13 $ 983,626.32 $ 1,012,734.57 $ 1,042,690.06 Value $24,035,125.95
10 in-line tenants $ 900,000.00 $ 927,000.00 $ 954,810.00 $ 983,454.30 $ 1,012,957.93 $ 1,043,346.67 $ 1,074,647.07 $ 1,106,886.48 $ 1,140,093.07 $ 1,174,295.87 $ 1,209,524.74
Total rent $ 3,525,500.00 $ 3,630,785.00 $ 3,739,155.95 $ 3,850,703.98 $ 3,965,522.91 $ 4,083,709.37 $ 4,205,362.84 $ 4,330,585.75 $ 4,459,483.60 $ 4,592,164.98 $ 4,728,741.74
Vacancy and collection losses $ (352,550.00) $ (363,078.50) $ (373,915.60) $ (385,070.40) $ (396,552.29) $ (408,370.94) $ (420,536.28) $ (433,058.58) $ (445,948.36) $ (459,216.50) $ (472,874.17)
EGI $ 3,172,950.00 $ 3,267,706.50 $ 3,365,240.35 $ 3,465,633.58 $ 3,568,970.62 $ 3,675,338.43 $ 3,784,826.55 $ 3,897,527.18 $ 4,013,535.24 $ 4,132,948.48 $ 4,255,867.57 4) Unleveraged IRR
Operating Expenses $ (1,269,180.00) $ (1,307,082.60) $ (1,346,096.14) $ (1,386,253.43) $ (1,427,588.25) $ (1,470,135.37) $ (1,513,930.62) $ (1,559,010.87) $ (1,605,414.09) $ (1,653,179.39) $ (1,702,347.03)
Net Operating Income $ 1,903,770.00 $ 1,960,623.90 $ 2,019,144.21 $ 2,079,380.15 $ 2,141,382.37 $ 2,205,203.06 $ 2,270,895.93 $ 2,338,516.31 $ 2,408,121.14 $ 2,479,769.09 $ 2,553,520.54
DS $ 1,464,438.46 $ 1,464,438.46 $ 1,464,438.46 $ 1,464,438.46 $ 1,464,438.46 $ 1,464,438.46 $ 1,464,438.46 $ 1,464,438.46 $ 1,464,438.46 $ 1,464,438.46 $ 1,464,438.46 Year 0 1 2 3 4 5 6 7 8 Year 9
BTCF $ 439,331.54 $ 496,185.44 $ 554,705.75 $ 614,941.69 $ 676,943.91 $ 740,764.60 $ 806,457.47 $ 874,077.84 $ 943,682.68 $ 17,882,011.46 NOI $ (4,508,375.30) $ 1,903,770.00 $ 1,960,623.90 $ 2,019,144.21 $ 2,079,380.15 $ 2,141,382.37 $ 2,205,203.06 $ 2,270,895.93 $ 2,338,516.31 NOI $ – 0
ATCF $ 289,660.56 $ 316,723.91 $ 344,364.35 $ 372,587.97 $ 401,400.50 $ 430,807.24 $ 460,813.03 $ 491,422.21 $ 522,638.59 $ 17,421,146.18
DCR $ 1.30 $ 1.34 $ 1.38 $ 1.42 $ 1.46 $ 1.51 $ 1.55 $ 1.60 $ 1.64 $ 1.69
Equity CF $ (4,508,375.30) $ 439,331.54 $ 496,185.44 $ 554,705.75 $ 614,941.69 $ 676,943.91 $ 740,764.60 $ 806,457.47 $ 874,077.84 $ 943,682.68 $ 17,882,011.46 IRR 41.98%
Total Equity CF $ (4,508,375.30) $ 289,660.56 $ 316,723.91 $ 344,364.35 $ 372,587.97 $ 401,400.50 $ 430,807.24 $ 460,813.03 $ 491,422.21 $ 522,638.59 $ 17,421,146.18
Depreciation $ (564,102.56) $ (564,102.56) $ (564,102.56) $ (564,102.56) $ (564,102.56) $ (564,102.56) $ (564,102.56) $ (564,102.56) $ (564,102.56) $ (564,102.56)
Beg Principle $ 18,491,624.70 $ 17,939,222.29 $ 17,358,557.93 $ 16,748,185.68 $ 16,106,585.62 $ 15,432,160.09 $ 14,723,229.67 $ 13,978,029.02 $ 13,194,702.49 $ 12,371,299.49
End Principle $ 17,939,222.29 $ 17,358,557.93 $ 16,748,185.68 $ 16,106,585.62 $ 15,432,160.09 $ 14,723,229.67 $ 13,978,029.02 $ 13,194,702.49 $ 12,371,299.49 $ 11,505,769.64
Principle paid $ 552,402.41 $ 580,664.36 $ 610,372.25 $ 641,600.06 $ 674,425.53 $ 708,930.42 $ 745,200.65 $ 783,326.53 $ 823,403.00 $ 865,529.86
Intrest $ (912,036.06) $ (883,774.10) $ (854,066.21) $ (822,838.41) $ (790,012.93) $ (755,508.04) $ (719,237.81) $ (681,111.93) $ (641,035.46) $ (598,908.60)
Taxable income $ 427,631.38 $ 512,747.24 $ 600,975.44 $ 692,439.18 $ 787,266.88 $ 885,592.46 $ 987,555.55 $ 1,093,301.81 $ 1,202,983.12 $ 1,316,757.92
Taxes $ (149,670.98) $ (179,461.53) $ (210,341.40) $ (242,353.71) $ (275,543.41) $ (309,957.36) $ (345,644.44) $ (382,655.63) $ (421,044.09) $ (460,865.27)
5) BTIRR 22.83%
ATIRR 19.31%

,

FIN 435 – Fall 2021

Mini Case

Part 2

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.