# 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