#What if analysis excel 2010 goal seek manual#
Step 2: Setting up the condition for Goal SeekĪlternatively, we use the ‘goal seek’ function to replicate the manual process of testing different debt limits (cell F20) that will reduce the debt balance to zero.
We will need to manually key in a lower limit until cell C19 is zero. Let’s say we start with a limit of $500m – the CFADS is not enough to repay the loan so there is still a ‘balance C/f maturity’ (cell 19) of $264m. In our downloadable workbook attached, we have a project with irregular cash flows, which will be used to solve for the maximum debt limit to achieve the target DSCR of 1.50x in every repayment period. Goal Seek to achieve maximum debt limit example
The Goal Seek function is simple to use because it only requires three inputs: What are the benefits and limitations of Goal Seek in sizing debt in project finance?
Goal Seek is an in-built function in Excel and is used when you know the desired result of a single formula but not the input value the formula needs to achieve the result. Alternatively, we can use the Goal Seek function in Excel to perform the manual process quickly and efficiently. We can adjust the debt limit manually so that the closing debt balance, at maturity, is zero. There are several methods that can achieve that in a financial model. In project finance, it is common practice to solve for an indicative maximum debt limit that can be supported by a project. Why and when would you use Goal Seek for Debt Sizing?
#What if analysis excel 2010 goal seek trial#
I can try and use some trial and error by putting random values in Loan amount, but there is a better way to do this – using Goal Seek in Excel. Now, I want to calculate the loan amount so that my monthly payment is $1,000. I use the following PMT formula in cell B4 to calculate the Monthly payment: =PMT(B2/12,B3,B1) Goal Seek in Excel, as the name suggests, helps you in achieving a value (the goal) by altering a dependent value.įor example, I have the data (as shown below), where I calculate the Monthly payment outflow with the given Loan Amount, Interest Rate and Number of Payments.