Accounting Excel worksheet

Instructions 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