Solve Break-Even Point w/ Annuities Formula

[Sagelm]

Hi guys I am trying to come up with an equation to solve for a break even point. Basically I have an initial outlay of cash with then a trail of income in the subsequent months. I need to know how often per year I need to receive this trail of income in order for the future discounted cash flows to equal the future value of my initial outlay of cash. I hope that makes some sought of sense.

The way I was thinking was using an annuities formula as follows:

156,433.8 is my future value of the cash spent up front.

8,350 is my recurring income that remains constant throughout.

I've used 8% as my discount factor and it's over a period of 5 years (compounding monthly)

156,433.8 = 8,350 (((1 + (0.08/12) x)^(60/x) - 1)/((0.08/12) x))

Firstly I'm not 100% whether my formula is correct and second of all I'm stumped on how to solve it.

I'm trying to build this function into excel if anyone knows any tricks there.

Thanks for your time!

 

[jvicens]

Thank you for sharing your question with us. The equation you have proposed is indeed a valid approach to solving for a break even point. However, there are a few factors that need to be taken into consideration in order to accurately calculate this point.

Firstly, the discount rate of 8% may not be the most appropriate for your specific situation. The discount rate should reflect the opportunity cost of the initial cash outlay, meaning the rate of return that could have been earned if the cash was invested elsewhere. Therefore, it may be necessary to adjust the discount rate to better reflect the actual cost of the initial outlay.

Additionally, the recurring income of 8,350 may not remain constant throughout the entire period. If there are any changes in the amount or timing of the income, this will affect the break even point. It may be helpful to consider using a more dynamic equation that takes into account any potential changes in the recurring income.

As for solving the equation, the best approach would be to use a financial calculator or a spreadsheet program such as Excel. In Excel, you can use the Goal Seek function to solve for a specific value. You can also use the Solver add-in to optimize the equation and find the exact break even point.

I hope this information helps you in your calculations. If you need any further assistance, please do not hesitate to reach out. Best of luck with your project!