NSAdventurer
New Member
- Joined
- Nov 30, 2015
- Messages
- 1
Excel Version: Office for Mac 2016
Good day,
I have a project that requires three rounds of equity investment (Tranche A, B, and C) and several loans (Loan 1, 2, 3, and 4). The project is expected to start generating a constant income on a monthly basis which will need to be divided in the following priority (debt repayment, profits, equity (after a certain point). It looks like this for example:
Equity:
Tranche A - $10,000,000 in January 2016
Tranche B - $10,000,000 in January 2017
Tranche C - $5,000,000 in January 2018
Debt:
Loan 1 - $1,200,000 advanced monthly May 2017 - October 2017 (6 equal payments)
Loan 1 - $1,200,000 advanced monthly May 2019 - October 2019 (6 equal payments)
Loan 1 - $1,200,000 advanced monthly May 2020 - October 2020 (6 equal payments)
Income:
Starting July 2017
800,000 per month (broken down as:
500,000 per month costs
300,000 per month profit)
The costs are then divided in a ratio between debt and equity repayment. Initially, this would be 70% to debt repayment and 30% to equity repayment.
However, I want to be able to model different dates to start both the debt and equity repayments to allow for earnings to be retained to reduce debt requirements.
Profits will always be distributed once the income stream begins.
Additionally, I need Tranche A to be paid completely before Tranche B receives any repayment and I need to delay the profit payment for each Tranche until after they have started receiving payments for their equity.
I have managed to set this up in a fixed format, but I need to be able to model different scenarios (e.g. - only two Tranches of $12,500,000 in equity rather than 3 or specifically setting the start of debt repayment depending on the loan date). I do not want loan 2 to start being repaid until the revenue resulting from the loan 2 investment is realized. This could leave some months between loans with no debt repayment.
I have started a new sheet where I use columns for the calculations, but I ended up confused on the formulas and IF statements that would be needed.
Any help would be greatly appreciated.
Good day,
I have a project that requires three rounds of equity investment (Tranche A, B, and C) and several loans (Loan 1, 2, 3, and 4). The project is expected to start generating a constant income on a monthly basis which will need to be divided in the following priority (debt repayment, profits, equity (after a certain point). It looks like this for example:
Equity:
Tranche A - $10,000,000 in January 2016
Tranche B - $10,000,000 in January 2017
Tranche C - $5,000,000 in January 2018
Debt:
Loan 1 - $1,200,000 advanced monthly May 2017 - October 2017 (6 equal payments)
Loan 1 - $1,200,000 advanced monthly May 2019 - October 2019 (6 equal payments)
Loan 1 - $1,200,000 advanced monthly May 2020 - October 2020 (6 equal payments)
Income:
Starting July 2017
800,000 per month (broken down as:
500,000 per month costs
300,000 per month profit)
The costs are then divided in a ratio between debt and equity repayment. Initially, this would be 70% to debt repayment and 30% to equity repayment.
However, I want to be able to model different dates to start both the debt and equity repayments to allow for earnings to be retained to reduce debt requirements.
Profits will always be distributed once the income stream begins.
Additionally, I need Tranche A to be paid completely before Tranche B receives any repayment and I need to delay the profit payment for each Tranche until after they have started receiving payments for their equity.
I have managed to set this up in a fixed format, but I need to be able to model different scenarios (e.g. - only two Tranches of $12,500,000 in equity rather than 3 or specifically setting the start of debt repayment depending on the loan date). I do not want loan 2 to start being repaid until the revenue resulting from the loan 2 investment is realized. This could leave some months between loans with no debt repayment.
I have started a new sheet where I use columns for the calculations, but I ended up confused on the formulas and IF statements that would be needed.
Any help would be greatly appreciated.
