The shops are fully leased at rates ranging from $$36-$$$52$ per SF$/$yr$.$ The average rent is $$42$ per SF$.$ The leases were all signed last year and thus expire at the end of year $4.$ Rents increase at $2\%$ per year. TI$$s are about $$25$ per SF$.$ Shop leases have a $5-$year term. There is significant deferred maintenance on the property which will need to be done in year $1$ including a new roof $($$$75,000),$ termite work $($$$25,000)$ and broken parking lot lights $($$$20,000).$ In addition, a new plaza is proposed for the center of the project which will cost $$25,000.$ Since the property is over $20$ years old, reserves for replacement are estimated at $3\%$ of EGI. Jorge has analyzed the parking ratio of the center. The site has $50$ parking spaces in excess of the $5\mathrm{.}0/1000$ SF ratio that the City requires. Jorge wants to convert a portion of the parking lot into a new pad for a $5,000$ SF Corner Bakery which will use up $25$ parking spaces. The new pad will need $25$ parking spaces. The lease negotiation should be fast since there are several other restaurants that want the pad. The new ground lease would have $9$ months of free rent but the tenant would pay CAM. The expected lease rate is $$24\mathrm{.}00/$SF$/$yr$.$ with $10\%$ increases every $5$ years and a $15-$year term. There would be TI$$s to prepare the site for the tenant of $$50,000.$ It is estimated the lease will be signed at purchase but there will be $3$ months of vacancy before the lease commences. Jorge$$s company uses a range of collection loss from $1-3\%.$ The collection loss estimate for this property is $1\mathrm{.}5\%$ due to the strong tenant base. The company has a discount rate that is the inflation rate plus the cap rate. The income inflation rate is estimated at $2\mathrm{.}25\%$ with expense inflation of $2\mathrm{.}25\%.$ The selling broker has provided a cap rate survey for similar shopping centers that have sold in the $10-$mile radius. The estimated cap rate is $5\mathrm{.}75\%.$ Sales costs are $6\%.$ Jorge$$s boss has asked him to do the following: $1)$ Prepare an unleveraged spreadsheet that calculates the PGI, EGI, Expenses, NOI and an IRR and NPV for a $$20,000,000$ sales price $2)$ Prepare a loan amortization table and add leverage to the above spreadsheet and calculate a leveraged IRR and NPV$.3)$ Calculate a value of the property with close to a $0$ NPV on an unleveraged basis and determine the leveraged IRR and NPV with its own spreadsheet. $4)$ Prepare a memo with the results of the analysis of the two spreadsheets, the bid price a list of items missing and a recommendation on whether to purchase the property.