Accounting Excel worksheet
fkanmsharon
ExcelAcctspreed.xlsxInstructions Test 1
| Vanke Holdings USA LLC- Excel Test | ||
| Instructions: | ||
| Refer to the "Cash Flow" worksheet in this document | ||
| Task 1. Calculate and complete all of the financing cost line items in the monthly cash flow | ||
| Assumptions: | ||
| * 65% LTC construction loan carrying a rate of L + 375bps | ||
| * 75 bps origination fee | ||
| * Mortgage recording tax of 280bps | ||
| * Mortgage broker fee of 50bps | ||
| * Other financing costs of 50bps | ||
| * The loan is paid off in full in December 2018 | ||
| Task 2. At the bottom of the "Cash Flow" worksheet, solve for: a) the interest expense for the life of the loan and b) when the first draw will occur | ||
| Task 3. Present the sources & uses for the project on the "Sources & Uses" worksheet | ||
| Task 4. Create sensitivities identifying loan amounts and total interest expense assuming LTC of 50%, 55%, 60%, 65% & 75% and rate spreads of 300bps, 325bps, 350bps, 375bps, & 400bps on the "Sensitivity Table" worksheet | ||
| Hint: | ||
| All of the financing costs will be capitazlied in the development budget and the LTC calculation should include financing costs. (This will cause a circular reference) |
Cash Flow
| Vanke Holdings USA LLC- Excel Test | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| Period | 0 | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | 11 | 12 | 13 | 14 | 15 | 16 | 17 | 18 | 19 | 20 | 21 | 22 | 23 | 24 | 25 | 26 | 27 | 28 | 29 | 30 | 31 | 32 | 33 | 34 | 35 | 36 | 37 | 38 | 39 | 40 | 41 | 42 | 43 | 44 | 45 | 46 | 47 | 48 | 49 | 50 | 51 | 52 | 53 | 54 | 55 | |||||
| Total Budget | 12/31/14 | 1/31/15 | 2/28/15 | 3/31/15 | 4/30/15 | 5/31/15 | 6/30/15 | 7/31/15 | 8/31/15 | 9/30/15 | 10/31/15 | 11/30/15 | 12/31/15 | 1/31/16 | 2/29/16 | 3/31/16 | 4/30/16 | 5/31/16 | 6/30/16 | 7/31/16 | 8/31/16 | 9/30/16 | 10/31/16 | 11/30/16 | 12/31/16 | 1/31/17 | 2/28/17 | 3/31/17 | 4/30/17 | 5/31/17 | 6/30/17 | 7/31/17 | 8/31/17 | 9/30/17 | 10/31/17 | 11/30/17 | 12/31/17 | 1/31/18 | 2/28/18 | 3/31/18 | 4/30/18 | 5/31/18 | 6/30/18 | 7/31/18 | 8/31/18 | 9/30/18 | 10/31/18 | 11/30/18 | 12/31/18 | 1/31/19 | 2/28/19 | 3/31/19 | 4/30/19 | 5/31/19 | 6/30/19 | 7/31/19 | |||||
| Total Land Costs | $50,000,000 | $50,000,000 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | ||||
| Total Hard Costs | $55,500,000 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $500,000 | $500,000 | $500,000 | $500,000 | $2,000,000 | $2,000,000 | $2,000,000 | $5,000,000 | $5,000,000 | $5,000,000 | $5,000,000 | $5,000,000 | $5,000,000 | $5,000,000 | $2,000,000 | $2,000,000 | $2,000,000 | $2,000,000 | $2,000,000 | $500,000 | $500,000 | $500,000 | $500,000 | $500,000 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | ||||
| Total Soft Costs | $6,700,000 | $0 | $0 | $0 | $0 | $200,000 | $200,000 | $200,000 | $200,000 | $500,000 | $500,000 | $500,000 | $500,000 | $500,000 | $500,000 | $500,000 | $200,000 | $200,000 | $200,000 | $200,000 | $200,000 | $200,000 | $200,000 | $200,000 | $200,000 | $200,000 | $200,000 | $200,000 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | ||||||||||||||
| Financing Costs | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| Origination Fee | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | ||||
| MRT | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | ||||
| Mortgage Broker Fee | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | ||||
| Other Financing Costs | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | ||||
| Interest Expense | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | ||||
| Total Financing Costs | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | ||||
Sources & Uses
| Vanke Holdings USA LLC- Excel Test |
Sensitivity Table
| Vanke Holdings USA LLC- Excel Test |
Instructions Test 2
| Vanke Holdings USA LLC- Excel Test | |||||||||||||||||||||||||||||||||||||
| Instructions: | |||||||||||||||||||||||||||||||||||||
| Review the cash flows below | |||||||||||||||||||||||||||||||||||||
| Assumptions: | |||||||||||||||||||||||||||||||||||||
| * The General Partner invests 10% of the required capital | |||||||||||||||||||||||||||||||||||||
| * The Limited Partner invests the remainder | |||||||||||||||||||||||||||||||||||||
| Task 1. Calculate the internal rate of return for the project | |||||||||||||||||||||||||||||||||||||
| Task 2. Calculate the capital commitment required of the GP and the LP | |||||||||||||||||||||||||||||||||||||
| Task 3. Calculate the cash flows, total profit and IRR to the GP and LP assuming the below promote structure | |||||||||||||||||||||||||||||||||||||
| Promote Structure Assumptions: | |||||||||||||||||||||||||||||||||||||
| * Level 1: Return of capital and 10% preferred return to both partners | |||||||||||||||||||||||||||||||||||||
| * Level 2: 100% of cash flow to the GP until the GP has received 20% of the total profit distributed cummulatively through Level 1+2 (the "Catch-Up") | |||||||||||||||||||||||||||||||||||||
| * Level 3: 80% to LP, 20% to GP until LP has achieved a 20% IRR, thereafter | |||||||||||||||||||||||||||||||||||||
| * Level 4: 50% to LP, 50% to GP | |||||||||||||||||||||||||||||||||||||
| Total | 12/31/14 | 1/31/15 | 2/28/15 | 3/31/15 | 4/30/15 | 5/31/15 | 6/30/15 | 7/31/15 | 8/31/15 | 9/30/15 | 10/31/15 | 11/30/15 | 12/31/15 | 1/31/16 | 2/29/16 | 3/31/16 | 4/30/16 | 5/31/16 | 6/30/16 | 7/31/16 | 8/31/16 | 9/30/16 | 10/31/16 | 11/30/16 | 12/31/16 | 1/31/17 | 2/28/17 | 3/31/17 | 4/30/17 | 5/31/17 | 6/30/17 | 7/31/17 | 8/31/17 | ||||
| Project Cash Flow | $154,000 | ($100,000) | $2,000 | $2,000 | $2,000 | $2,000 | $2,000 | $2,000 | $2,000 | ($5,000) | ($10,000) | ($20,000) | ($50,000) | ($25,000) | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $50,000 | $50,000 | $50,000 | $50,000 | $50,000 | $50,000 | $50,000 |
