please show your work on excel

1) What did Plimsol pay for the Note? Your can use QUARTERLY data (not necessary to use XIRR since all cash flows will Become Conventional) On March 10, 2006, HDP Development, LLC purchased a 30-unit apartment property located in the vibrant neighborhood of North Hollywood (Los Angeles, California), for a total consideration of $4,525,045 or $150,834 per unit. The subject property was originally built in 1985 and consists of a three-story building totaling approximately 27,950 square feet of gross building area (GBA). The units range from 755 to 927 square feet rising over a ground floor parking garage providing 60 parking spaces. The property was purchased with the intent of converting into a for-sale condominium project which required an estimated $1,300,000 in hard costs, $300,000 in soft costs and $500,000 in carrying costs. The transaction was financed with a Note provided by First Commercial Bank of Hollywood, HDP was forecasting sale prices to average $330,000 per unit. The Borrower ran into some issues obtaining a final condo map from the City of Los Angeles and sales did not start until November 2007. By then, the real estate market had already turned sour: as of March 2008, only six units sold at prices varying between $250,000 and $315,000, depending on the size of the units. A declining economy with tighter underwriting standards and the oversupply of for-sale properties were main reasons for the housing market's softening. Prospective homebuyers were unable to obtain credit, which had a significant impact in the volume of buying activity in the market. According to DQNews.com, the median price for a single-family residence as of December 2008 in Los Angeles was $334,500, down 39.2% from the previous year. As sales activity slowed dramatically, the loan interest reserve depleted and HDP ran out of money. The bank elected to restructure the loan rather than classify it as a non-performing asset on its balance sheet, in to avoid regulatory oversight. On September 15, 2008, the Borrower and the Bank executed a loan modification which reset the unpaid principal balance, and called for the principal balance plus accruing interest to be paid by April 5, 2009. By April 5, 2009, no new sales closed and the loan matured. The bank elected to immediately file a Notice of Default and commenced the foreclosure process. Starting on the date of the NOD, the loan balance started accruing at the prescribed default rate. The bank short of liquidities due to the real estate downturn chose to market the note for sale. On April 30, 2009, PLIMSOL Real Estate Fund, LLC purchased the promissory note and obligations therewith. PLIMSOL, an experienced private equity fund focusing on the purchase and restructuring of non-performing real estate assets, immediately negotiated a forbearance agreement which essentially forced the Borrower to sell the units at reduced prices to payoff its loan obligation in lieu of foreclosure. The forbearance, which was executed on May 12, 2009, called for the foreclosure to be postponed as long as the Borrower sells a minimum average rate of 1 unit per month, over the next 22 months; with minimum net proceeds of $185,000 per unit. The 22 remaining units were sold before the two years and are accounted on a quarterly cash flow basis. A forbearance is not a loan modification, therefore the Borrower is still in default and interest accrues at the default rate. The DEFAULT rate is 10% annual interest on the unpaid balance. The borrower sold units prior to the sale of the note, thus the original loan was paid down to $3,500,000. (unpaid balance due). HDP reduced its asking prices to an average of $225,000 per unit and sold off the units in the prescribed time frame. On November 15, 2009 HDP paid off its loan obligation in full at the sale of the 22nd unit. (SEE SCHEDULE BELOW, after the essay question, BY ESCROW CLOSING PERIOD) PLIMSOL successfully restructured the loan and the booked a pre-tax annual internal rate of return on its investment of 125 % calculated using quarterly cash flows. The CFO of PLIMSOL tells you that $150,000 at closing of the loan purchase brought taxes current, which amount was added to the loan balance. Taxes then accrued unpaid until close of escrow at an average of $2250 per unit per year. Other operating costs are covered 80% by the HOA fees, paid monthly at a rate of $280 per unit per month, paid at the end of each quarterly period. Both taxes and HOA fees are prorated at closing. Sale commissions and closing costs averaged 6% of gross sale prices. PLIMSOL's CFO is looking for a new financial analyst and determines that one who can determine the actual purchase price of the promissory note . Assume money for each sale is distributed back into the Fund at the close of each quarterly period. Further assume all other costs also are paid quarterly as well. Please simply use quarterly data with all cash flows occurring at the end of the period. For computational purposes, assume all cash flows (including the interest on the loan work out and amortization) occur at the end of each quarter. No. Unit # Purchase Price Closing Costs Net Proceeds2 Quarter 1 101 $219,000 (13,140) 205,860 1 6 106 $195,000 (11,700) 183,300 1 11 112 $195,000 (11,700) 183,300 1 14 115 $219,000 (13,140) 205,860 1 13 114 $200,000 (12,000) 188,000 1 23 213 $220,000 (13,200) 206,800 Q 1 ends 4 104 $225,000 (13,500) 211,500 2 15 202 $225,000 (13,500) 211,500 2 16 204 $225,000 (13,500) 211, 2 22 212 $225,000 (13,500) 211,500 2 24 215 $230,000 (13,800) 216,200 2 21 211 $220,000 (13,200) 206,800 Q2 ends 7 108 $220,000 (13,200) 206,800 3 18 208 $233,000 (13,980) 219,020 3 5 105 $225,000 (13,500) 211,500 3 20 210 $225,000 (13,500) 211,500 3 19 209 $230,000 (13,800) 216,200 3 17 205 $225,000 (13,500) 211,500 Q 3 ends 2 102 $225,000 (13,500) 211,500 4 10 111 $225,000 (13,500) 211,500 4 12 113 $233,000 (13,980) 219,020 4 9 110 $226,000 (13,560) 212,440 Quarter 4 ends 1) What did Plimsol pay for the Note? Your can use QUARTERLY data (not necessary to use XIRR since all cash flows will Become Conventional) On March 10, 2006, HDP Development, LLC purchased a 30-unit apartment property located in the vibrant neighborhood of North Hollywood (Los Angeles, California), for a total consideration of $4,525,045 or $150,834 per unit. The subject property was originally built in 1985 and consists of a three-story building totaling approximately 27,950 square feet of gross building area (GBA). The units range from 755 to 927 square feet rising over a ground floor parking garage providing 60 parking spaces. The property was purchased with the intent of converting into a for-sale condominium project which required an estimated $1,300,000 in hard costs, $300,000 in soft costs and $500,000 in carrying costs. The transaction was financed with a Note provided by First Commercial Bank of Hollywood, HDP was forecasting sale prices to average $330,000 per unit. The Borrower ran into some issues obtaining a final condo map from the City of Los Angeles and sales did not start until November 2007. By then, the real estate market had already turned sour: as of March 2008, only six units sold at prices varying between $250,000 and $315,000, depending on the size of the units. A declining economy with tighter underwriting standards and the oversupply of for-sale properties were main reasons for the housing market's softening. Prospective homebuyers were unable to obtain credit, which had a significant impact in the volume of buying activity in the market. According to DQNews.com, the median price for a single-family residence as of December 2008 in Los Angeles was $334,500, down 39.2% from the previous year. As sales activity slowed dramatically, the loan interest reserve depleted and HDP ran out of money. The bank elected to restructure the loan rather than classify it as a non-performing asset on its balance sheet, in to avoid regulatory oversight. On September 15, 2008, the Borrower and the Bank executed a loan modification which reset the unpaid principal balance, and called for the principal balance plus accruing interest to be paid by April 5, 2009. By April 5, 2009, no new sales closed and the loan matured. The bank elected to immediately file a Notice of Default and commenced the foreclosure process. Starting on the date of the NOD, the loan balance started accruing at the prescribed default rate. The bank short of liquidities due to the real estate downturn chose to market the note for sale. On April 30, 2009, PLIMSOL Real Estate Fund, LLC purchased the promissory note and obligations therewith. PLIMSOL, an experienced private equity fund focusing on the purchase and restructuring of non-performing real estate assets, immediately negotiated a forbearance agreement which essentially forced the Borrower to sell the units at reduced prices to payoff its loan obligation in lieu of foreclosure. The forbearance, which was executed on May 12, 2009, called for the foreclosure to be postponed as long as the Borrower sells a minimum average rate of 1 unit per month, over the next 22 months; with minimum net proceeds of $185,000 per unit. The 22 remaining units were sold before the two years and are accounted on a quarterly cash flow basis. A forbearance is not a loan modification, therefore the Borrower is still in default and interest accrues at the default rate. The DEFAULT rate is 10% annual interest on the unpaid balance. The borrower sold units prior to the sale of the note, thus the original loan was paid down to $3,500,000. (unpaid balance due). HDP reduced its asking prices to an average of $225,000 per unit and sold off the units in the prescribed time frame. On November 15, 2009 HDP paid off its loan obligation in full at the sale of the 22nd unit. (SEE SCHEDULE BELOW, after the essay question, BY ESCROW CLOSING PERIOD) PLIMSOL successfully restructured the loan and the booked a pre-tax annual internal rate of return on its investment of 125 % calculated using quarterly cash flows. The CFO of PLIMSOL tells you that $150,000 at closing of the loan purchase brought taxes current, which amount was added to the loan balance. Taxes then accrued unpaid until close of escrow at an average of $2250 per unit per year. Other operating costs are covered 80% by the HOA fees, paid monthly at a rate of $280 per unit per month, paid at the end of each quarterly period. Both taxes and HOA fees are prorated at closing. Sale commissions and closing costs averaged 6% of gross sale prices. PLIMSOL's CFO is looking for a new financial analyst and determines that one who can determine the actual purchase price of the promissory note . Assume money for each sale is distributed back into the Fund at the close of each quarterly period. Further assume all other costs also are paid quarterly as well. Please simply use quarterly data with all cash flows occurring at the end of the period. For computational purposes, assume all cash flows (including the interest on the loan work out and amortization) occur at the end of each quarter. No. Unit # Purchase Price Closing Costs Net Proceeds2 Quarter 1 101 $219,000 (13,140) 205,860 1 6 106 $195,000 (11,700) 183,300 1 11 112 $195,000 (11,700) 183,300 1 14 115 $219,000 (13,140) 205,860 1 13 114 $200,000 (12,000) 188,000 1 23 213 $220,000 (13,200) 206,800 Q 1 ends 4 104 $225,000 (13,500) 211,500 2 15 202 $225,000 (13,500) 211,500 2 16 204 $225,000 (13,500) 211, 2 22 212 $225,000 (13,500) 211,500 2 24 215 $230,000 (13,800) 216,200 2 21 211 $220,000 (13,200) 206,800 Q2 ends 7 108 $220,000 (13,200) 206,800 3 18 208 $233,000 (13,980) 219,020 3 5 105 $225,000 (13,500) 211,500 3 20 210 $225,000 (13,500) 211,500 3 19 209 $230,000 (13,800) 216,200 3 17 205 $225,000 (13,500) 211,500 Q 3 ends 2 102 $225,000 (13,500) 211,500 4 10 111 $225,000 (13,500) 211,500 4 12 113 $233,000 (13,980) 219,020 4 9 110 $226,000 (13,560) 212,440 Quarter 4 ends