Codetree.com: Private Equity model
 Share
The version of the browser you are using is no longer supported. Please upgrade to a supported browser.Dismiss

 
View only
 
 
ABCDEFGHIJKLMNOPQRSTUVWXYZ
1
AssumptionsNotes
2
Debt interest rates
Interest Rate
Amort (months)
Change amort and interest rates
Instructions in blue
3
Bank debt7%84
Change the fields in yellow
4
Vendor Takeback interest rate5.0%48
Read the original article here: A framework for picking and funding your business idea
5
Other debt interest rate0.0%0
6
LP debt interest rate8.0%84
7
8
Earnings Tax Rates
Change to your tax rates
9
Tax rate < 500k13.5%
10
Tax rate >= 500k26%
11
12
Acqusition Info
Change earnings, purchase price, and revenue
13
EBIT$1,000,000
14
Purchase price$3,000,000.00
15
Multiple of EBIT for valuation3.00
16
Revenue1,400,000
17
18
Financing% of priceAmount% of EBIT
Change the percentages financed per source
19
Debt Financing ↓
20
Bank debt10%$300,000.0030.00%
21
Vendor take back5%$150,000.0015.00%
22
Other debt0%$0.000.00%
23
Interest only LP debt0%$0.000.00%
24
Total Debt15%$450,000.00
25
26
Equity Financing ↓
27
GP Equity30%$765,000.0076.50%
28
LP Equity70%$1,785,000.00178.50%
29
Total Equity85%$2,550,000.00255.00%
30
Total Financed100%$3,000,000.00300.00%
31
32
33
At Purchase
End of Year 1
End of Year 2
End of Year 3
End of Year 4
End of Year 5
End of Year 6
End of Year 7
34
Year01234567
35
Debt, as % of EBIT45.00%35%27%19%11%7%3%0%
36
37
Growth
38
ACV$2,400.00$2,400.00$2,400.00$2,400.00$2,400.00$2,400.00$2,400.00Change ACV
39
Churn Rate2%2%2%2%2%2%2%
Change churn rate
40
Growth Rate8%8%8%8%8%8%8%
Change growth rate
41
Total Customers583618655695736781827
42
New Customers47474952565962
43
Churned Customers12121213141516
44
Net New Customers35353739424447
45
Net New Revenue$84,000.00$84,000.00$89,040.00$94,382.40$100,045.34$106,048.06$112,410.95
46
Expenses to service new customers
10%10%10%10%10%10%10%
Change expenses to serve newly-acquired customers
47
New Expenses$8,400.00$8,400.00$8,904.00$9,438.24$10,004.53$10,604.81$11,241.09
48
Net New EBIT$75,600.00$75,600.00$80,136.00$84,944.16$90,040.81$95,443.26$101,169.85
49
50
Earnings
51
EBIT1,000,000 1,075,600 1,151,200 1,231,336 1,316,280 1,406,321 1,501,764 1,602,934
52
53
Debt
54
Bank debt$300,000$265,576$228,663$189,082$146,640$101,129$52,328$0
55
Vendor Takeback$150,000$115,258$78,739$40,352-$0-$0-$0-$0
56
Other debt$0$0$0$0$0$0$0$0
57
LP Debt$0$0$0$0$0$0$0$0
58
Total Debt$450,000$380,834$307,402$229,434$146,640$101,129$52,328$0
59
60
Loan Payments (Principal+Interest)
61
Bank debt$54,334$54,334$54,334$54,334$54,334$54,334$54,334
62
Vendor Takeback$41,453$41,453$41,453$41,453$0$0$0
63
Other debt$0$0$0$0$0$0$0
64
LP debt$0$0$0$0$0$0$0
65
Total Loan Payments$95,786$95,786$95,786$95,786$54,334$54,334$54,334
66
67
Interest Payments
68
Bank debt$19,909$17,421$14,753$11,891$8,823$5,533$2,005
69
Vendor Takeback$6,711$4,934$3,065$1,101$0$0$0
70
Other debt$0$0$0$0$0$0$0
71
LP debt$0$0$0$0$0$0$0
72
Annual Interest Payments$26,620$22,355$17,818$12,992$8,823$5,533$2,005
73
74
Taxes
75
Profit before taxes$1,048,980$1,128,845$1,213,518$1,303,288$1,397,498$1,496,231$1,600,929
76
Taxes210,235 231,000 253,015 276,355 300,849 326,520 353,741
77
Profit after taxes838,745 897,846 960,504 1,026,933 1,096,648 1,169,711 1,247,187
78
79
Payouts
80
Debt holders95,786 95,786 95,786 95,786 54,334 54,334 54,334
81
Dividends to GP equity holders222,888240,618259,415279,344312,694334,613357,856
82
Dividends to LP equity holders520,071561,442605,302651,803729,620780,764834,998
83
Total Dividends to equity holders
742,958 802,059 864,717 931,147 1,042,315 1,115,377 1,192,854
84
Total payments to security holders
838,745 897,846 960,504 1,026,933 1,096,648 1,169,711 1,247,187
85
86
Book Value
87
Book value of debt 450,000 354,067 267,028 186,329 111,405 71,910 34,845 0
88
Book value of equity 2,550,000 2,550,000 2,550,000 2,550,000 2,550,000 2,550,000 2,550,000 2,550,000
89
Book value of the firm 3,000,000 2,904,067 2,817,028 2,736,329 2,661,405 2,621,910 2,584,845 2,550,000
90
91
Returns
92
Return on total capital28.8%29.8%30.7%31.6%32.5%33.0%33.5%33.9%
93
Return on equity0.0%29.1%31.5%33.9%36.5%40.9%43.7%46.8%
94
95
96
97
98
99
100
Loading...
 
 
 
Sheet1