Plastics in Space
JohnnetworkCopyofltv-worksheet.xlsx
LTV Template
Assumptions | Entries | Notes | ||||||
One Time Sale | ||||||||
Average Initial Sale | $0 | This model assumes that Initial purchase is made at beginning of the year 0. | ||||||
Gross Margin - Initial Sale | 0.0% | If there is no initial purchase, enter $0 for Average Initial Sale. | ||||||
Next Product Purchase Rate | 0% | |||||||
Next Product Purchase Year | 0 | If product is purchased 1 or mores times a year, use the Periodic Purchases section below | ||||||
Periodic Purchases | Customer buys 1 or more times a year | |||||||
Yearly Revenue | $0 | Typical of many businesses | ||||||
Gross Margin - Yearly Revenue | 0.0% | |||||||
Retention Rate | 0.0% | |||||||
Recurring Revenue | Customer pays on a recurring basis | |||||||
Average Recurring Revenue | $0 | Revenue per billing period | ||||||
Gross Margin - Recurring Revenue | 0.0% | Customer pays on a recurring basis | ||||||
# months of recurring in Year 1 | 0 | Could be a monthly subscription | ||||||
# months of recurring in Years 2-5 | 0 | Could be a quarterly subscription box | ||||||
Retention Rate | 0.0% | |||||||
Addon Sales | Any other sales that do not fit in above categories | |||||||
Yearly Addon Sales - Year 1 start | $0 | |||||||
Gross Margin - Addon sales | 0.0% | |||||||
Retention Rate | 0.0% | |||||||
Cost of Capital | Assume 50% if giving up equity | |||||||
Cost of Capital | 50% | If borrowing money, use 12% | ||||||
Day | Year | Year | Year | Year | Year | |||
LTV Calculation | 0 | 1 | 2 | 3 | 4 | 5 | ||
Initial Sale Revenue | $0 | $0 | $0 | $0 | $0 | $0 | ||
Gross Margin - Initial Sale | 0% | 0% | 0% | 0% | 0% | 0% | ||
Repurchase Rate | 0% | 0% | 0% | 0% | 0% | |||
Profit from initial sale | $0 | $0 | $0 | $0 | $0 | $0 | ||
Periodic Purchases - Yearly Revenue | $0 | $0 | $0 | $0 | $0 | |||
Retention Rate | 0% | 0% | 0% | 0% | 0% | |||
Cumulative Retention Rate | 0% | 0% | 0% | 0% | 0% | |||
Gross Margin for Yearly Revenue | 0% | 0% | 0% | 0% | 0% | Gross margin is the difference between revenue and cost of goods sold (COGS) divided by revenue. | ||
Profit from Yearly Revenue | $0 | $0 | $0 | $0 | $0 | $0 | ||
Recurring Revenue | $0 | $0 | $0 | $0 | $0 | |||
Retention Rate | 0% | 0% | 0% | 0% | 0% | |||
Cumulative Retention Rate | 0% | 0% | 0% | 0% | 0% | |||
Gross Margin for Recurring | 0% | 0% | 0% | 0% | 0% | |||
Profit from Recurring | $0 | $0 | $0 | $0 | $0 | $0 | ||
Addon Sales Revenue | $0 | $0 | $0 | $0 | $0 | |||
Retention Rate | 0% | 0% | 0% | 0% | 0% | |||
Cumulative Retention Rate | 0% | 0% | 0% | 0% | 0% | |||
Gross Margin for Addon sales | 0% | 0% | 0% | 0% | 0% | |||
Profit from Addon sales | $0 | $0 | $0 | $0 | $0 | $0 | ||
Total Profits | $0 | $0 | $0 | $0 | $0 | $0 | ||
Cost of capital rate | 50% | 50% | 50% | 50% | 50% | 50% | ||
Net Present Value Factor | 1.0000 | 0.6667 | 0.4444 | 0.2963 | 0.1975 | 0.1317 | ||
Present value of Profits | $0 | $0 | $0 | $0 | $0 | $0 | ||
LTV/Net Present Value of Profits | $0 | |||||||
LTV/CAC ratio | 3 | 4 | 5 | 6 | 8 | |||
CAC (Cost of Customer Acquisition) | $0.00 | $0.00 | $0.00 | $0.00 | $0.00 | |||
Example 1
Example - periodic purchases with a quarterly subscription box | ||||||
Assumptions | Entries | Notes | ||||
One Time Sale | ||||||
Average Initial Sale | $0 | This model assumes that Initial purchase is made at beginning of the year 0. | ||||
Gross Margin - Initial Sale | 0.0% | If there is no initial purchase, enter $0 for Average Initial Sale. | ||||
Next Product Purchase Rate | 0% | |||||
Next Product Purchase Year | 0 | If product is purchased 1 or mores times a year, use the Periodic Purchases section below | ||||
Periodic Purchases | Customer buys 1 or more times a year | |||||
Yearly Revenue | $32 | Typical of many businesses | ||||
Gross Margin - Yearly Revenue | 68.0% | |||||
Retention Rate | 80.0% | |||||
Recurring Revenue | Customer pays on a recurring basis | |||||
Average Recurring Revenue | $40 | Revenue per billing period | ||||
Gross Margin - Recurring Revenue | 68.0% | Customer pays on a recurring basis | ||||
# months of recurring in Year 1 | 4 | Could be a monthly subscription | ||||
# months of recurring in Years 2-5 | 4 | Could be a quarterly subscription box | ||||
Retention Rate | 80.0% | |||||
Addon Sales | Any other sales that do not fit in above categories | |||||
Yearly Addon Sales - Year 1 start | $0 | |||||
Gross Margin - Addon sales | 0.0% | |||||
Retention Rate | 0.0% | |||||
Cost of Capital | Assume 50% if giving up equity | |||||
Cost of Capital | 12% | If borrowing money, use 12% | ||||
Day | Year | Year | Year | Year | Year | |
LTV Calculation | 0 | 1 | 2 | 3 | 4 | 5 |
Initial Sale Revenue | $0 | $0 | $0 | $0 | $0 | $0 |
Gross Margin - Initial Sale | 0% | 0% | 0% | 0% | 0% | 0% |
Repurchase Rate | 0% | 0% | 0% | 0% | 0% | |
Profit from initial sale | $0 | $0 | $0 | $0 | $0 | $0 |
Periodic Purchases - Yearly Revenue | $32 | $32 | $32 | $32 | $32 | |
Retention Rate | 80% | 80% | 80% | 80% | 80% | |
Cumulative Retention Rate | 80% | 64% | 51% | 41% | 33% | |
Gross Margin for Yearly Revenue | 68% | 68% | 68% | 68% | 68% | |
Profit from Yearly Revenue | $0 | $17 | $14 | $11 | $9 | $7 |
Recurring Revenue | $160 | $160 | $160 | $160 | $160 | |
Retention Rate | 80% | 80% | 80% | 80% | 80% | |
Cumulative Retention Rate | 80% | 64% | 51% | 41% | 33% | |
Gross Margin for Recurring | 68% | 68% | 68% | 68% | 68% | |
Profit from Recurring | $0 | $87 | $70 | $56 | $45 | $36 |
Addon Sales Revenue | $0 | $0 | $0 | $0 | $0 | |
Retention Rate | 0% | 0% | 0% | 0% | 0% | |
Cumulative Retention Rate | 0% | 0% | 0% | 0% | 0% | |
Gross Margin for Addon sales | 0% | 0% | 0% | 0% | 0% | |
Profit from Addon sales | $0 | $0 | $0 | $0 | $0 | $0 |
Total Profits | $0 | $104 | $84 | $67 | $53 | $43 |
Cost of capital rate | 12% | 12% | 12% | 12% | 12% | 12% |
Net Present Value Factor | 1.0000 | 0.8929 | 0.7972 | 0.7118 | 0.6355 | 0.5674 |
Present value of Profits | $0 | $93 | $67 | $48 | $34 | $24 |
LTV/Net Present Value of Profits | $266 | |||||
LTV/CAC ratio | 3 | 4 | 5 | 6 | 8 | |
CAC (Cost of Customer Acquisition) | $88.57 | $66.43 | $53.14 | $44.29 | $33.21 | |
Example 2
Example - recurring revenue only - monthly subscription | ||||||
Assumptions | Entries | Notes | ||||
One Time Sale | ||||||
Average Initial Sale | $0 | This model assumes that Initial purchase is made at beginning of the year 0. | ||||
Gross Margin - Initial Sale | 0.0% | If there is no initial purchase, enter $0 for Average Initial Sale. | ||||
Next Product Purchase Rate | 0% | |||||
Next Product Purchase Year | 0 | If product is purchased 1 or mores times a year, use the Periodic Purchases section below | ||||
Periodic Purchases | Customer buys 1 or more times a year | |||||
Yearly Revenue | $0 | Typical of many businesses | ||||
Gross Margin - Yearly Revenue | 0.0% | |||||
Retention Rate | 0.0% | |||||
Recurring Revenue | Customer pays on a recurring basis | |||||
Average Recurring Revenue | $20 | Revenue per billing period | ||||
Gross Margin - Recurring Revenue | 95.0% | Customer pays on a recurring basis | ||||
# months of recurring in Year 1 | 12 | Could be a monthly subscription | ||||
# months of recurring in Years 2-5 | 12 | Could be a quarterly subscription box | ||||
Retention Rate | 85.0% | |||||
Addon Sales | Any other sales that do not fit in above categories | |||||
Yearly Addon Sales - Year 1 start | $0 | |||||
Gross Margin - Addon sales | 0.0% | |||||
Retention Rate | 0.0% | |||||
Cost of Capital | Assume 50% if giving up equity | |||||
Cost of Capital | 50% | If borrowing money, use 12% | ||||
Day | Year | Year | Year | Year | Year | |
LTV Calculation | 0 | 1 | 2 | 3 | 4 | 5 |
Initial Sale Revenue | $0 | $0 | $0 | $0 | $0 | $0 |
Gross Margin - Initial Sale | 0% | 0% | 0% | 0% | 0% | 0% |
Repurchase Rate | 0% | 0% | 0% | 0% | 0% | |
Profit from initial sale | $0 | $0 | $0 | $0 | $0 | $0 |
Periodic Purchases - Yearly Revenue | $0 | $0 | $0 | $0 | $0 | |
Retention Rate | 0% | 0% | 0% | 0% | 0% | |
Cumulative Retention Rate | 0% | 0% | 0% | 0% | 0% | |
Gross Margin for Yearly Revenue | 0% | 0% | 0% | 0% | 0% | |
Profit from Yearly Revenue | $0 | $0 | $0 | $0 | $0 | $0 |
Recurring Revenue | $240 | $240 | $240 | $240 | $240 | |
Retention Rate | 85% | 85% | 85% | 85% | 85% | |
Cumulative Retention Rate | 85% | 72% | 61% | 52% | 44% | |
Gross Margin for Recurring | 95% | 95% | 95% | 95% | 95% | |
Profit from Recurring | $0 | $194 | $165 | $140 | $119 | $101 |
Addon Sales Revenue | $0 | $0 | $0 | $0 | $0 | |
Retention Rate | 0% | 0% | 0% | 0% | 0% | |
Cumulative Retention Rate | 0% | 0% | 0% | 0% | 0% | |
Gross Margin for Addon sales | 0% | 0% | 0% | 0% | 0% | |
Profit from Addon sales | $0 | $0 | $0 | $0 | $0 | $0 |
Total Profits | $0 | $194 | $165 | $140 | $119 | $101 |
Cost of capital rate | 50% | 50% | 50% | 50% | 50% | 50% |
Net Present Value Factor | 1.0000 | 0.6667 | 0.4444 | 0.2963 | 0.1975 | 0.1317 |
Present value of Profits | $0 | $129 | $73 | $41 | $24 | $13 |
LTV/Net Present Value of Profits | $281 | |||||
LTV/CAC ratio | 3 | 4 | 5 | 6 | 8 | |
CAC (Cost of Customer Acquisition) | $93.58 | $70.18 | $56.15 | $46.79 | $35.09 | |
Example 3
Example - 1-time sale and monthly recurring revenue | |||||||||
Assumptions | Entries | Notes | |||||||
One Time Sale | |||||||||
Average Initial Sale | $10,000 | This model assumes that Initial purchase is made at beginning of the year 0. | |||||||
Gross Margin - Initial Sale | 65.0% | If there is no initial purchase, enter $0 for Average Initial Sale. | Gross margin is the difference between revenue and cost of goods sold (COGS) divided by revenue. | ||||||
Next Product Purchase Rate | 75% | ||||||||
Next Product Purchase Year | 5 | If product is purchased 1 or mores times a year, use the Periodic Purchases section below | |||||||
Periodic Purchases | Customer buys 1 or more times a year | ||||||||
Yearly Revenue | $0 | Typical of many businesses | |||||||
Gross Margin - Yearly Revenue | 0.0% | ||||||||
Retention Rate | 0.0% | ||||||||
Recurring Revenue | Customer pays on a recurring basis | ||||||||
Average Recurring Revenue | $125 | Revenue per billing period | |||||||
Gross Margin - Recurring Revenue | 85.0% | Customer pays on a recurring basis | |||||||
# months of recurring in Year 1 | 6 | Could be a monthly subscription | |||||||
# months of recurring in Years 2-5 | 12 | Could be a quarterly subscription box | |||||||
Retention Rate | 90.0% | ||||||||
Addon Sales | Any other sales that do not fit in above categories | ||||||||
Yearly Addon Sales - Year 1 start | $0 | ||||||||
Gross Margin - Addon sales | 0.0% | ||||||||
Retention Rate | 0.0% | ||||||||
Cost of Capital | Assume 50% if giving up equity | ||||||||
Cost of Capital | 50% | If borrowing money, use 12% | |||||||
Day | Year | Year | Year | Year | Year | ||||
LTV Calculation | 0 | 1 | 2 | 3 | 4 | 5 | |||
Initial Sale Revenue | $10,000 | $0 | $0 | $0 | $0 | $10,000 | |||
Gross Margin - Initial Sale | 65% | 65% | 65% | 65% | 65% | 65% | |||
Repurchase Rate | 75% | 75% | 75% | 75% | 75% | ||||
Profit from initial sale | $6,500 | $0 | $0 | $0 | $0 | $4,875 | |||
Periodic Purchases - Yearly Revenue | $0 | $0 | $0 | $0 | $0 | ||||
Retention Rate | 0% | 0% | 0% | 0% | 0% | ||||
Cumulative Retention Rate | 0% | 0% | 0% | 0% | 0% | ||||
Gross Margin for Yearly Revenue | 0% | 0% | 0% | 0% | 0% | ||||
Profit from Yearly Revenue | $0 | $0 | $0 | $0 | $0 | $0 | |||
Recurring Revenue | $750 | $1,500 | $1,500 | $1,500 | $1,500 | ||||
Retention Rate | 90% | 90% | 90% | 90% | 90% | ||||
Cumulative Retention Rate | 90% | 81% | 73% | 66% | 59% | ||||
Gross Margin for Recurring | 85% | 85% | 85% | 85% | 85% | ||||
Profit from Recurring | $0 | $574 | $1,033 | $929 | $837 | $753 | |||
Addon Sales Revenue | $0 | $0 | $0 | $0 | $0 | ||||
Retention Rate | 0% | 0% | 0% | 0% | 0% | ||||
Cumulative Retention Rate | 0% | 0% | 0% | 0% | 0% | ||||
Gross Margin for Addon sales | 0% | 0% | 0% | 0% | 0% | ||||
Profit from Addon sales | $0 | $0 | $0 | $0 | $0 | $0 | |||
Total Profits | $6,500 | $574 | $1,033 | $929 | $837 | $5,628 | |||
Cost of capital rate | 50% | 50% | 50% | 50% | 50% | 50% | |||
Net Present Value Factor | 1.0000 | 0.6667 | 0.4444 | 0.2963 | 0.1975 | 0.1317 | |||
Present value of Profits | $6,500 | $383 | $459 | $275 | $165 | $741 | |||
LTV/Net Present Value of Profits | $8,523 | ||||||||
LTV/CAC ratio | 3 | 4 | 5 | 6 | 8 | ||||
CAC (Cost of Customer Acquisition) | $2,841.09 | $2,130.81 | $1,704.65 | $1,420.54 | $1,065.41 | ||||
Cost of Equity Capital
https://www.planprojections.com/funding/cost-of-equity-financing/ | |
Formula is i=(FV/PV)^(1/n)-1 | |
Enter the first 5 values and the cost of equity is calculated | |
Factor | |
PV - Equity Investment | 70,000 |
% equity purchased | 40% |
Value of business at disposal | 940,000 |
FV = Value of equity at disposal | 376,000 |
n - number of years to disposal | 5 |
i = cost of equity | 40% |
Churn
Churn at different rates | |||||
Churn Rate | 2% | 5% | 10% | 15% | 20% |
Initial # customers | 100 | 100 | 100 | 100 | 100 |
Period 1 | 98.0 | 95.0 | 90.0 | 85.0 | 80.0 |
Period 2 | 96.0 | 90.3 | 81.0 | 72.3 | 64.0 |
Period 3 | 94.1 | 85.7 | 72.9 | 61.4 | 51.2 |
Period 4 | 92.2 | 81.5 | 65.6 | 52.2 | 41.0 |
Period 5 | 90.4 | 77.4 | 59.0 | 44.4 | 32.8 |
Period 6 | 88.6 | 73.5 | 53.1 | 37.7 | 26.2 |
Period 7 | 86.8 | 69.8 | 47.8 | 32.1 | 21.0 |
Period 8 | 85.1 | 66.3 | 43.0 | 27.2 | 16.8 |
Period 9 | 83.4 | 63.0 | 38.7 | 23.2 | 13.4 |
Period 10 | 81.7 | 59.9 | 34.9 | 19.7 | 10.7 |
Period 11 | 80.1 | 56.9 | 31.4 | 16.7 | 8.6 |
Period 12 | 78.5 | 54.0 | 28.2 | 14.2 | 6.9 |
Period 13 | 76.9 | 51.3 | 25.4 | 12.1 | 5.5 |
Period 14 | 75.4 | 48.8 | 22.9 | 10.3 | 4.4 |
Period 15 | 73.9 | 46.3 | 20.6 | 8.7 | 3.5 |
Period 16 | 72.4 | 44.0 | 18.5 | 7.4 | 2.8 |
Period 17 | 70.9 | 41.8 | 16.7 | 6.3 | 2.3 |
Period 18 | 69.5 | 39.7 | 15.0 | 5.4 | 1.8 |
Period 19 | 68.1 | 37.7 | 13.5 | 4.6 | 1.4 |
Period 20 | 66.8 | 35.8 | 12.2 | 3.9 | 1.2 |
Period 21 | 65.4 | 34.1 | 10.9 | 3.3 | 0.9 |
Period 22 | 64.1 | 32.4 | 9.8 | 2.8 | 0.7 |
Period 23 | 62.8 | 30.7 | 8.9 | 2.4 | 0.6 |
Period 24 | 61.6 | 29.2 | 8.0 | 2.0 | 0.5 |
Period 25 | 60.3 | 27.7 | 7.2 | 1.7 | 0.4 |
Period 26 | 59.1 | 26.4 | 6.5 | 1.5 | 0.3 |
Period 27 | 58.0 | 25.0 | 5.8 | 1.2 | 0.2 |
Period 28 | 56.8 | 23.8 | 5.2 | 1.1 | 0.2 |
Period 29 | 55.7 | 22.6 | 4.7 | 0.9 | 0.2 |
Period 30 | 54.5 | 21.5 | 4.2 | 0.8 | 0.1 |
Period 31 | 53.5 | 20.4 | 3.8 | 0.6 | 0.1 |
Period 32 | 52.4 | 19.4 | 3.4 | 0.6 | 0.1 |
Period 33 | 51.3 | 18.4 | 3.1 | 0.5 | 0.1 |
Period 34 | 50.3 | 17.5 | 2.8 | 0.4 | 0.1 |
Period 35 | 49.3 | 16.6 | 2.5 | 0.3 | 0.0 |
Period 36 | 48.3 | 15.8 | 2.3 | 0.3 | 0.0 |