Spaces:
Sleeping
Sleeping
| FormFormulas: | |
| 1. E1 (Building Efficiency)= E2/D2= gross square feet/ rentable square feet eg 71,920/108,270 | |
| 2. D4 (Land Value Per GSF)= $c4/D$2= total land value/ total GSF | |
| 3. E4 (Land Value per RSF)=$c4/E$2= total land value/ total rsf | |
| 4. F4 (Land Value per Unit)= $C4/F$2= total land value/ total units | |
| 5. D5 (Closing Costs per GSF)= $C5/D$2= total closing costs/ total GSF | |
| 6. E5 (Closing Costs per RSF)= $C5/E$2= total closing costs/total RSF | |
| 7. F5 (Closing Costs per Unit) = $C5/F$2= total closing costs/ total units | |
| 8. C6 ( Acquisition Fee)=C4*0.02= 2% of total land value | |
| 9. D6 (Acq fee per GSF)=$C6/D$2= Acq fee/ total GSF | |
| 10. E6 (Acq fee per RSF)=$C6/E$2= Acq fee/ total RSF | |
| 11. F6 (Acq fee per unit)=$C6/F$2= Acq fee/ total units | |
| 12. C7 (Total cost of acquisition)=SUM(C4:C6) | |
| 13. D7 (Total Acq cost per GSF)=$C7D$2(SUM(D4:D6))= total acq cost/ total GSF | |
| 14. E7 (Total Acq cost per RSF)=$C7/E$2(SUM(E4:E6))= total acq cost/ total RSF | |
| 15. F7 (total acq cost per unit)=$C7/F$2= total acq cost/ total units | |
| 16. C10 (Total construction GMP)=A10*D2= 338? x 108,270= 338? x GSF | |
| 17. D10 (Construction GMP per GSF)=$C10/D$2= Construction GMP/ total GSF | |
| 18. E10 (Construction GMP per RSF)=$C10/E$2)= Construction GMP/ Total RSF | |
| 19. F10( Construction GMP per Unit)= $C10/F$2)= Construction GMP/ total units | |
| 20. C33 (HPD & IH cost)=(3500*F2*0.75)+(5000*F2*0.25) | |
| 21. C34 (Retail TI & LC cost)=J41*0.3)+(50*I40) | |
| 22. C35(Total soft cost)=SUM(C13:C34) | |
| 23. D13(Arch&Int cost per GSF)=$C13/D$2= Arch&Int cost/total GSF | |
| 24. D14(Struct Engin cost per GSF)=$C14/D$2= struct Engin cost/total GSF | |
| 25. D15(MEP Engin cost per GSF)=$C15/D$2= MEP Engin cost/ total GSF | |
| 26. D16 (BPP & Civil Engineer cost per GSF)=$C16/D$2= BPP & Civ Engineer cost/ total GSF | |
| 27. D17(Controlled Inspections cost per GSF)=$C17/D$2= controlled inspections cost/total GSF | |
| 28. D18(surveying cost per GSF)=$C18/D$2= Surverying cost/total GSF | |
| 29. D19 (Utilities cost Per GSF)= $C19/D$2= utilities cost/total GSF | |
| 30. D20 (Adv&Mark cost per GSF)=$C20/D$2= Adv&Mark cost/total GSF | |
| 31. D21(Acc cost per GSF)=$C21/D$2= Acc cost/total GSF | |
| 32. D22(Monitoring cost per GSF)=$C22/D$2=monitoring cost/total GSF | |
| 33. D23(FF&E cost per GSF)=$C23/D$2= FF&E cost/total GSF | |
| 34. D24(Environmental Consultant fee per GSF)= $C24/D$2= Environmental consultant fee/ total GSF | |
| 35. D25(Misc Consultants fee per GSF)=$C25/D$2 | |
| 36. D26(General Legal cost per GSF)= $C26/D$2 | |
| 37. D27 (RE taxes per GSF)= $C27/D$2 | |
| 38. D28(Misc Admin cost per GSF)=$C28/D$2 | |
| 39. D29(IBR cost per GSF)=$C29/D$2 | |
| 40. D30(Project team cost per GSF)=$C30/D$2 | |
| 41. D31(PEM Fees per GSF)=$C31/D$2 | |
| 42. D32(Bank fees per GSF)=$C32/D$2 | |
| 43. D33(HPD&IH fees per GSF)=$C33/D$2 | |
| 44. D34(Retail TI &LC fees per GSF)=$C34/D$2 | |
| 45. D35(Soft Cost per GSF)=$C35/D$2 | |
| 46. E13(Arch&Int cost per RSF)=$C13/E$2 | |
| 47. E14(Struct Engin cost per RSF)=$C14/E$2 | |
| 48. E15(MEP Engin cost per RSF)=$C15/E$2 | |
| 49. E16(BPP&Civil Engineer Cost per Rsf)=$C16/E$2 | |
| 50. E17(Controlled Inspections cost per RSF)=$C17/E$2 | |
| 51. E18(Surveying cost per RSF)=$C18/E$2 | |
| 52. E19(Utilities cost per RSF)=$C19/E$2 | |
| 53. E20(Ad&M cost per RSF)=$C20/E$2 | |
| 54. E21(Acc cost per RSf)=$C21/E$2 | |
| 55. E22(Mon cost per RSf)=$C22/E$2 | |
| 56. E23(FF&E per RSF)=$C23/E$2 | |
| 57. E24(Env con cost per RSF)=$C24/E$2 | |
| 58. E25(Misc con cost per rsf)=$C25/E$2 | |
| 59. E26(Gen legal cost per rsf)=$C26/E$2 | |
| 60. E27(RE taxes cost per RSF)=$C27/E$2 | |
| 61. E28(Misc Admin Cost per RSF)=$C28/E$2 | |
| 62. E29(IBR cost per RSf)=$C29/E$2 | |
| 63. E30(project team fees per RSf)=$C30/E$2 | |
| 64. E31(PEM fees per RSF)=$C31/E$2 | |
| 65. E32(banks fees per rsf)=$C32/E$2 | |
| 66. E33(HPD &IH fees per rsf)=$C33/E$2 | |
| 67. E34(Retail TI &LC cost per RSF)=$C34/E$2 | |
| 68. E35(Soft cost per RSf)= $C35/E$2 | |
| 69. F13(Arch&int cost per unit)=$C13/F$2 | |
| 70. F14(Structural engineer cost per unit)=$C14/F$2 | |
| 71. F15(MEP engin cost per unit)=$C15/F$2 | |
| 72. F16(BPP&civil engineer cost per unit)=$C16/F$2 | |
| 73. F17(CI cost per unit)=$C17/F$2 | |
| 74. F18(Surveying post per unit)=$C18/F$2 | |
| 75. F19(Utilities cost per unit)=$C19/F$2 | |
| 76. F20(Ad&M cost per unit)=$C20/F$2 | |
| 77. F21(Acc cost per unit)=$C21/F$2 | |
| 78. F22(Mon cost per unit)=$C22/F$2 | |
| 79. F23(FF&E cost per unit)=$C23/F$2 | |
| 80. F24(Env con cost per unit)=$C24/F$2 | |
| 81. F25(Misc con cost per unit)=$C25/F$2 | |
| 82. F26(Gen legal cost per unit)=$C26/F$2 | |
| 83. F27(Re taxes per unit)=$C27/F$2 | |
| 84. F28(Misc admin cost per unit)=$C28/F$2 | |
| 85. F29(IBR cost per unit)=$C29/F$2 | |
| 86. F30(Project team cost per unit)=$C30/F$2 | |
| 87. F31(PEM fees per unit=$C30/F$2 | |
| 88. F32(bank fees per unit)=$C32/F$2 | |
| 89. F33(HPD&IH fees per unit)=$C33/F$2 | |
| 90. F34(Retail TI & LC cost per unit)=$C34/F$2 | |
| 91. F35(Soft cost per unit)=$C35/F$2 | |
| 92. C38(Contingency cost)=A38*($C$35+$C$10)= 5% of Construction GMP+total Soft Cost | |
| 93. C39( Dev fee)=A39*($C$35+$C$10)= 4% of Con GMP+total soft cost | |
| 94. C42(Op reserve)=M15*0.2= 20% of total expenses | |
| 95. C44( total Financing Contigency and Reserves)=C35+C10+SUM(C38:C42)+C7= total soft cost+ con GMP+ Acq cost+ total fin con res) | |
| 96. D38(Cont per GSf)=$C38/D$2 | |
| 97. D39( dev fee per Gsf)=$C39/D$2 | |
| 98. D40(Fin cost per GSF)=$C40/D$2 | |
| 99. D41(IR per GSF)=$C41/D$2 | |
| 100. D42(Op res per GSF)=$C42/D$2 | |
| 101. D44(Total fin con reserves per gsf)=$C44/D$2 | |
| 102. E38(Cont cost per RSf)=$C38/E$2 | |
| 103. E39(Dev fee per rsf)=$C39/E$2 | |
| 104. E40(Fin cost per rsf)=$C40/E$2 | |
| 105. E41(IR per RSF)=$C41/E$2 | |
| 106. E42(Op res per Rsf)=$C42/E$2 | |
| 107. E44(Total fin con reserves per rsf)=$C44/E$2 | |
| 108. F38(Cont cost per unit)=$C38/F$2 | |
| 109. F39(dev fee per unit)=$C39/F$2 | |
| 110. F40(Fin costs per unit)=$C40/F$2 | |
| 111. F41(IR per unit)=$C41/F$2 | |
| 112. F42(op res per unit)=$C42/F$2 | |
| 113. F44(Total fin con reserves per unit)=$C44/F$2 | |
| 114. J3(Pre LTC Budget)=(C35+C38+C39+C42+C10+C7) | |
| 115. J5(True LTC)=J5=M3=True LTC=Debt % | |
| 116. J7(Loan Amount)=J4*J3 | |
| 117. J9(Financing Amt)=J8*J7 | |
| 118. J11(Interest Rate)=(J10+430)/10000 | |
| 119. J13(J7*0.7*(J11/12)*J12) | |
| 120. J18(Trending Term)=M22+M23 | |
| 121. J19(Term revenue Inflation)=(1+J16)^($J$18/12) | |
| 122. J20(Term Expense Inflation)=(1+J17)^($J$18/12) | |
| 123. J28(Interest rate)=(J27+430)/10000 | |
| 124. J34(Gross Potential FM Rent)=M33*0.75*E2 | |
| 125. J35(Gross Potential IH Rent)=M33*0.75*E2 | |
| 126. J36(Other income)=I36*F2*12*0.75 | |
| 127. J37(Vacancy)=I37*(J36+J34+35) | |
| 128. J38(Effective Gross income)=J34-J37+J36+J35 | |
| 129. J41(Retail rev)=J40*I40 | |
| 130. J44(Management Fee)=I44*J38 | |
| 131. J45(RE Taxes)=D2*30*0.1 | |
| 132. J46(Total Expenses)=SUM(J43:J45) | |
| 133. J48(NOI)=J38-J46+J39+J41 | |
| 134. J49(Stabilized Yield on Cost)=(((J38+J41-J35)*J19)-(J46*J20))+J35 | |
| 135. J50(Yield on cost %)=J48/($C$44+PV(J21/12,36,0,C27)) | |
| 136. J51(Stabilized Yield on Cost %)=J49/($C$44+PV(J21/12,36,0,C27)) | |
| 137. J54(Property Value on Sale+ 25% of Year 1 CF)=(J49/I54)+(J49*0.25) | |
| 138. J55(Sale Cost)=I55*J54 | |
| 139. J56(Net Sale Proceeds)=J54-J55 | |
| 140. J57(Cash Remaining After Loan Payback)=J56-N3 | |
| 141. J58(Lp Investment)=N4-J59 | |
| 142. J59(GP investment)=N4*0.2 | |
| 143. J61(GP Pref Accrual + Principal)=(1+I61/12)^$I$60*J59 | |
| 144. J62(LP Pref Accrual + Principal)=(1+I62/12)^$I$60*J58 | |
| 145. J63(Cash remaining after pref)=J57-J62-J61 | |
| 146. J64(Promote on JV)=I64*J63 | |
| 147. J65(Cash to LP)=(J63-J64)*(J58/(J58+J59)) | |
| 148. J66(Net to LP Investor)=J65+J62 | |
| 149. M3= (Debt%)=N3/C44 | |
| 150. M4=(Equity%)=1-M3 | |
| 151. M5=(Total Cap stack%)=M3+M4 | |
| 152. N3(Debt)=J13+J7+J9 | |
| 153. N4(Equity)=C44-N3 | |
| 154. N5( Cap stack )=N3+N4 | |
| 155. O3(Debt per Gsf)=$N3/D$2 | |
| 156. O4(Equity per GSf)=$N4/D$2 | |
| 157. O5(Cap stack per Gsf)=$N5/D$2 | |
| 158. P3(Debt per Rsf)=$N3/E$2 | |
| 159. P4(Equity per Rsf)=$N4/E$2 | |
| 160. P5(Cap stack per Rsf)=$N5/E$2 | |
| 161. Q3(Debt per unit)=$N3/F$2 | |
| 162. Q4(Equity per unit)=$N4/F$2 | |
| 163. Q5(Cap Stack per unit)=$N5/F$2 | |
| 164. M10(R&M turnover)=Q10*$F$2 | |
| 165. M11(Utilities)=Q11*$F$2 | |
| 166. M12(Admin+Pro)= Q12*$F$2 | |
| 167. M13 (insurance)=Q13*$F$2 | |
| 168. M14(Pro fees)=Q14*$F$2 | |
| 169. M15(Total Expenses)=SUM(M9:M14) | |
| 170. N9(Payroll %)=M9/$J$38 | |
| 171. N10(R&M turnover %)=M10/$J$38 | |
| 172. N11(Utilities cost %)=M11/$J$38 | |
| 173. N12(Admin+Pro %)=M12/$J$38 | |
| 174. N13(Insurance %)=M13/$J$38 | |
| 175. N14(Pro fees%)=M14/$J$38 | |
| 176. N15(total expenses %)= M15/$J$38 | |
| 177. N29(Start Date)=MIn(N21:N28) | |
| 178. M35(Blended RSF)=M33*0.75+M34*0.25 | |
| 179. N33(Total Free market rent)=$M$33*425/12 | |
| 180. N35(total Blended RSF)=$M$33*750/12 | |
| 181. O33(Free market rents Sf)=N33/*110/12 | |
| 182. O34(Affordable rents Sf)=N34*110/12 | |
| O35(Blended RSF sf)=N35*110/12ulas: | |
| 1. E1 (Building Efficiency)= E2/D2= gross square feet/ rentable square feet eg 71,920/108,270 | |
| 2. D4 (Land Value Per GSF)= $c4/D$2= total land value/ total GSF | |
| 3. E4 (Land Value per RSF)=$c4/E$2= total land value/ total rsf | |
| 4. F4 (Land Value per Unit)= $C4/F$2= total land value/ total units | |
| 5. D5 (Closing Costs per GSF)= $C5/D$2= total closing costs/ total GSF | |
| 6. E5 (Closing Costs per RSF)= $C5/E$2= total closing costs/total RSF | |
| 7. F5 (Closing Costs per Unit) = $C5/F$2= total closing costs/ total units | |
| 8. C6 ( Acquisition Fee)=C4*0.02= 2% of total land value | |
| 9. D6 (Acq fee per GSF)=$C6/D$2= Acq fee/ total GSF | |
| 10. E6 (Acq fee per RSF)=$C6/E$2= Acq fee/ total RSF | |
| 11. F6 (Acq fee per unit)=$C6/F$2= Acq fee/ total units | |
| 12. C7 (Total cost of acquisition)=SUM(C4:C6) | |
| 13. D7 (Total Acq cost per GSF)=$C7D$2(SUM(D4:D6))= total acq cost/ total GSF | |
| 14. E7 (Total Acq cost per RSF)=$C7/E$2(SUM(E4:E6))= total acq cost/ total RSF | |
| 15. F7 (total acq cost per unit)=$C7/F$2= total acq cost/ total units | |
| 16. C10 (Total construction GMP)=A10*D2= 338? x 108,270= 338? x GSF | |
| 17. D10 (Construction GMP per GSF)=$C10/D$2= Construction GMP/ total GSF | |
| 18. E10 (Construction GMP per RSF)=$C10/E$2)= Construction GMP/ Total RSF | |
| 19. F10( Construction GMP per Unit)= $C10/F$2)= Construction GMP/ total units | |
| 20. C33 (HPD & IH cost)=(3500*F2*0.75)+(5000*F2*0.25) | |
| 21. C34 (Retail TI & LC cost)=J41*0.3)+(50*I40) | |
| 22. C35(Total soft cost)=SUM(C13:C34) | |
| 23. D13(Arch&Int cost per GSF)=$C13/D$2= Arch&Int cost/total GSF | |
| 24. D14(Struct Engin cost per GSF)=$C14/D$2= struct Engin cost/total GSF | |
| 25. D15(MEP Engin cost per GSF)=$C15/D$2= MEP Engin cost/ total GSF | |
| 26. D16 (BPP & Civil Engineer cost per GSF)=$C16/D$2= BPP & Civ Engineer cost/ total GSF | |
| 27. D17(Controlled Inspections cost per GSF)=$C17/D$2= controlled inspections cost/total GSF | |
| 28. D18(surveying cost per GSF)=$C18/D$2= Surverying cost/total GSF | |
| 29. D19 (Utilities cost Per GSF)= $C19/D$2= utilities cost/total GSF | |
| 30. D20 (Adv&Mark cost per GSF)=$C20/D$2= Adv&Mark cost/total GSF | |
| 31. D21(Acc cost per GSF)=$C21/D$2= Acc cost/total GSF | |
| 32. D22(Monitoring cost per GSF)=$C22/D$2=monitoring cost/total GSF | |
| 33. D23(FF&E cost per GSF)=$C23/D$2= FF&E cost/total GSF | |
| 34. D24(Environmental Consultant fee per GSF)= $C24/D$2= Environmental consultant fee/ total GSF | |
| 35. D25(Misc Consultants fee per GSF)=$C25/D$2 | |
| 36. D26(General Legal cost per GSF)= $C26/D$2 | |
| 37. D27 (RE taxes per GSF)= $C27/D$2 | |
| 38. D28(Misc Admin cost per GSF)=$C28/D$2 | |
| 39. D29(IBR cost per GSF)=$C29/D$2 | |
| 40. D30(Project team cost per GSF)=$C30/D$2 | |
| 41. D31(PEM Fees per GSF)=$C31/D$2 | |
| 42. D32(Bank fees per GSF)=$C32/D$2 | |
| 43. D33(HPD&IH fees per GSF)=$C33/D$2 | |
| 44. D34(Retail TI &LC fees per GSF)=$C34/D$2 | |
| 45. D35(Soft Cost per GSF)=$C35/D$2 | |
| 46. E13(Arch&Int cost per RSF)=$C13/E$2 | |
| 47. E14(Struct Engin cost per RSF)=$C14/E$2 | |
| 48. E15(MEP Engin cost per RSF)=$C15/E$2 | |
| 49. E16(BPP&Civil Engineer Cost per Rsf)=$C16/E$2 | |
| 50. E17(Controlled Inspections cost per RSF)=$C17/E$2 | |
| 51. E18(Surveying cost per RSF)=$C18/E$2 | |
| 52. E19(Utilities cost per RSF)=$C19/E$2 | |
| 53. E20(Ad&M cost per RSF)=$C20/E$2 | |
| 54. E21(Acc cost per RSf)=$C21/E$2 | |
| 55. E22(Mon cost per RSf)=$C22/E$2 | |
| 56. E23(FF&E per RSF)=$C23/E$2 | |
| 57. E24(Env con cost per RSF)=$C24/E$2 | |
| 58. E25(Misc con cost per rsf)=$C25/E$2 | |
| 59. E26(Gen legal cost per rsf)=$C26/E$2 | |
| 60. E27(RE taxes cost per RSF)=$C27/E$2 | |
| 61. E28(Misc Admin Cost per RSF)=$C28/E$2 | |
| 62. E29(IBR cost per RSf)=$C29/E$2 | |
| 63. E30(project team fees per RSf)=$C30/E$2 | |
| 64. E31(PEM fees per RSF)=$C31/E$2 | |
| 65. E32(banks fees per rsf)=$C32/E$2 | |
| 66. E33(HPD &IH fees per rsf)=$C33/E$2 | |
| 67. E34(Retail TI &LC cost per RSF)=$C34/E$2 | |
| 68. E35(Soft cost per RSf)= $C35/E$2 | |
| 69. F13(Arch&int cost per unit)=$C13/F$2 | |
| 70. F14(Structural engineer cost per unit)=$C14/F$2 | |
| 71. F15(MEP engin cost per unit)=$C15/F$2 | |
| 72. F16(BPP&civil engineer cost per unit)=$C16/F$2 | |
| 73. F17(CI cost per unit)=$C17/F$2 | |
| 74. F18(Surveying post per unit)=$C18/F$2 | |
| 75. F19(Utilities cost per unit)=$C19/F$2 | |
| 76. F20(Ad&M cost per unit)=$C20/F$2 | |
| 77. F21(Acc cost per unit)=$C21/F$2 | |
| 78. F22(Mon cost per unit)=$C22/F$2 | |
| 79. F23(FF&E cost per unit)=$C23/F$2 | |
| 80. F24(Env con cost per unit)=$C24/F$2 | |
| 81. F25(Misc con cost per unit)=$C25/F$2 | |
| 82. F26(Gen legal cost per unit)=$C26/F$2 | |
| 83. F27(Re taxes per unit)=$C27/F$2 | |
| 84. F28(Misc admin cost per unit)=$C28/F$2 | |
| 85. F29(IBR cost per unit)=$C29/F$2 | |
| 86. F30(Project team cost per unit)=$C30/F$2 | |
| 87. F31(PEM fees per unit=$C30/F$2 | |
| 88. F32(bank fees per unit)=$C32/F$2 | |
| 89. F33(HPD&IH fees per unit)=$C33/F$2 | |
| 90. F34(Retail TI & LC cost per unit)=$C34/F$2 | |
| 91. F35(Soft cost per unit)=$C35/F$2 | |
| 92. C38(Contingency cost)=A38*($C$35+$C$10)= 5% of Construction GMP+total Soft Cost | |
| 93. C39( Dev fee)=A39*($C$35+$C$10)= 4% of Con GMP+total soft cost | |
| 94. C42(Op reserve)=M15*0.2= 20% of total expenses | |
| 95. C44( total Financing Contigency and Reserves)=C35+C10+SUM(C38:C42)+C7= total soft cost+ con GMP+ Acq cost+ total fin con res) | |
| 96. D38(Cont per GSf)=$C38/D$2 | |
| 97. D39( dev fee per Gsf)=$C39/D$2 | |
| 98. D40(Fin cost per GSF)=$C40/D$2 | |
| 99. D41(IR per GSF)=$C41/D$2 | |
| 100. D42(Op res per GSF)=$C42/D$2 | |
| 101. D44(Total fin con reserves per gsf)=$C44/D$2 | |
| 102. E38(Cont cost per RSf)=$C38/E$2 | |
| 103. E39(Dev fee per rsf)=$C39/E$2 | |
| 104. E40(Fin cost per rsf)=$C40/E$2 | |
| 105. E41(IR per RSF)=$C41/E$2 | |
| 106. E42(Op res per Rsf)=$C42/E$2 | |
| 107. E44(Total fin con reserves per rsf)=$C44/E$2 | |
| 108. F38(Cont cost per unit)=$C38/F$2 | |
| 109. F39(dev fee per unit)=$C39/F$2 | |
| 110. F40(Fin costs per unit)=$C40/F$2 | |
| 111. F41(IR per unit)=$C41/F$2 | |
| 112. F42(op res per unit)=$C42/F$2 | |
| 113. F44(Total fin con reserves per unit)=$C44/F$2 | |
| 114. J3(Pre LTC Budget)=(C35+C38+C39+C42+C10+C7) | |
| 115. J5(True LTC)=J5=M3=True LTC=Debt % | |
| 116. J7(Loan Amount)=J4*J3 | |
| 117. J9(Financing Amt)=J8*J7 | |
| 118. J11(Interest Rate)=(J10+430)/10000 | |
| 119. J13(J7*0.7*(J11/12)*J12) | |
| 120. J18(Trending Term)=M22+M23 | |
| 121. J19(Term revenue Inflation)=(1+J16)^($J$18/12) | |
| 122. J20(Term Expense Inflation)=(1+J17)^($J$18/12) | |
| 123. J28(Interest rate)=(J27+430)/10000 | |
| 124. J34(Gross Potential FM Rent)=M33*0.75*E2 | |
| 125. J35(Gross Potential IH Rent)=M33*0.75*E2 | |
| 126. J36(Other income)=I36*F2*12*0.75 | |
| 127. J37(Vacancy)=I37*(J36+J34+35) | |
| 128. J38(Effective Gross income)=J34-J37+J36+J35 | |
| 129. J41(Retail rev)=J40*I40 | |
| 130. J44(Management Fee)=I44*J38 | |
| 131. J45(RE Taxes)=D2*30*0.1 | |
| 132. J46(Total Expenses)=SUM(J43:J45) | |
| 133. J48(NOI)=J38-J46+J39+J41 | |
| 134. J49(Stabilized Yield on Cost)=(((J38+J41-J35)*J19)-(J46*J20))+J35 | |
| 135. J50(Yield on cost %)=J48/($C$44+PV(J21/12,36,0,C27)) | |
| 136. J51(Stabilized Yield on Cost %)=J49/($C$44+PV(J21/12,36,0,C27)) | |
| 137. J54(Property Value on Sale+ 25% of Year 1 CF)=(J49/I54)+(J49*0.25) | |
| 138. J55(Sale Cost)=I55*J54 | |
| 139. J56(Net Sale Proceeds)=J54-J55 | |
| 140. J57(Cash Remaining After Loan Payback)=J56-N3 | |
| 141. J58(Lp Investment)=N4-J59 | |
| 142. J59(GP investment)=N4*0.2 | |
| 143. J61(GP Pref Accrual + Principal)=(1+I61/12)^$I$60*J59 | |
| 144. J62(LP Pref Accrual + Principal)=(1+I62/12)^$I$60*J58 | |
| 145. J63(Cash remaining after pref)=J57-J62-J61 | |
| 146. J64(Promote on JV)=I64*J63 | |
| 147. J65(Cash to LP)=(J63-J64)*(J58/(J58+J59)) | |
| 148. J66(Net to LP Investor)=J65+J62 | |
| 149. M3= (Debt%)=N3/C44 | |
| 150. M4=(Equity%)=1-M3 | |
| 151. M5=(Total Cap stack%)=M3+M4 | |
| 152. N3(Debt)=J13+J7+J9 | |
| 153. N4(Equity)=C44-N3 | |
| 154. N5( Cap stack )=N3+N4 | |
| 155. O3(Debt per Gsf)=$N3/D$2 | |
| 156. O4(Equity per GSf)=$N4/D$2 | |
| 157. O5(Cap stack per Gsf)=$N5/D$2 | |
| 158. P3(Debt per Rsf)=$N3/E$2 | |
| 159. P4(Equity per Rsf)=$N4/E$2 | |
| 160. P5(Cap stack per Rsf)=$N5/E$2 | |
| 161. Q3(Debt per unit)=$N3/F$2 | |
| 162. Q4(Equity per unit)=$N4/F$2 | |
| 163. Q5(Cap Stack per unit)=$N5/F$2 | |
| 164. M10(R&M turnover)=Q10*$F$2 | |
| 165. M11(Utilities)=Q11*$F$2 | |
| 166. M12(Admin+Pro)= Q12*$F$2 | |
| 167. M13 (insurance)=Q13*$F$2 | |
| 168. M14(Pro fees)=Q14*$F$2 | |
| 169. M15(Total Expenses)=SUM(M9:M14) | |
| 170. N9(Payroll %)=M9/$J$38 | |
| 171. N10(R&M turnover %)=M10/$J$38 | |
| 172. N11(Utilities cost %)=M11/$J$38 | |
| 173. N12(Admin+Pro %)=M12/$J$38 | |
| 174. N13(Insurance %)=M13/$J$38 | |
| 175. N14(Pro fees%)=M14/$J$38 | |
| 176. N15(total expenses %)= M15/$J$38 | |
| 177. N29(Start Date)=MIn(N21:N28) | |
| 178. M35(Blended RSF)=M33*0.75+M34*0.25 | |
| 179. N33(Total Free market rent)=$M$33*425/12 | |
| 180. N35(total Blended RSF)=$M$33*750/12 | |
| 181. O33(Free market rents Sf)=N33/*110/12 | |
| 182. O34(Affordable rents Sf)=N34*110/12 | |
| 183. O35(Blended RSF sf)=N35*110/12 |