HBX- Accounting 7 Flashcards
Pro-Forma Financial Statements
Financial Statements forecasted for future periods. May also be referred to as a financial forecast or financial projection. In this course, we use pro-formas as a depiction of what the financial statements for the business will look like over a certain period of time, if the assumptions made when preparing them hold true.
Metropolis had net sales of ₹120 million in 2003 related to pathology services.
They expect sales of pathology services to grow by 75% in 2004.
Metropolis also plans to introduce a new service, Home Healthcare Blood Draws, which will generate sales of ₹10 million in 2004.
What will be the forecasted net sales for 2004?
₹120
₹10
₹220
₹75
₹220
Pathology revenue is expected to increase by 75% and Home Healthcare Blood Draws revenue is expected to add ₹10 million. In order to calculate, multiply the prior year’s revenue by 175% and add ₹10 million.
Calculation: (120.0 * 1.75) +10 = 220.0
Metropolis had net sales of ₹120 million in 2003 related to pathology services.
They expect sales of pathology services to grow by 75% in 2004.
Metropolis also plans to introduce a new service, Home Healthcare Blood Draws, which will generate sales of ₹10 million in 2004.
Metropolis expects sales of pathology services to grow by 75% in 2005.
Home Healthcare Blood Draws will generate sales of ₹20 million in 2005.
What will be the forecasted net sales for 2005?
HINT: You must compound the 2003 sales (₹120 million) twice rather than simply multiplying the 2004 revenue by 175% because the 2004 revenue includes ₹10 million of Home Healthcare Blood Draws revenue.
₹20
₹220
₹75
₹387.5
387.5
Pathology revenue is expected to increase by 75% and Home Healthcare Blood Draws revenue is expected to add ₹20 million. In order to calculate, multiply the 2003 revenue by 175% compounded twice and add ₹20 million.
Calculation: (120.0 * 1.75 * 1.75) + 20 = 387.5
The primary costs that make up the cost of sales for Metropolis are employee costs, chemical costs, and supplies costs. Employee costs are forecast to be 20% of revenue, chemical costs are forecast to be 20% of revenue, and supplies costs are forecast to be 5% of revenue, for a total of 45%. With forecasted sales revenue of ₹220 for 2004, what will be the forecasted cost of sales for 2004?
₹220
₹45
₹99
₹20
99
Cost of sales is forecast to be 45% of net sales (sum of employee cost, 20% + chemical cost, 20% + supplies cost, 5%). In order to calculate, multiply 2004 sales by 45%.
Calculation: (220.0 * 0.45) = 99.0
Variable costs
Variable costs are costs that rise and fall with revenue or activity. An example could be the cost of flour at a bakery which would tend to change in proportion with sales. It stands to reason that many current asset accounts, such as accounts receivable and inventory, and current liability accounts, such as accounts payable, would tend to increase or decrease proportionately with revenue.
What are the steps in creating a full Pro-Forma Statement?
- Determine the expected sales
- Look for trends and relationships among the various components of costs to forecast the cost of Sales & Operating Costs & Inventory
- Calculate Accounts Receivable . (Normally they’ll have a percentage for this. For example, 70% of sales is for credit purchases with a turn over of 6. This means to multiply revenue by .**7 and then divide by 6)
- Forcast Salaries and Wages Payable. This depends on the number of staff and the monetary value of the payroll as well as other factors such how often the employees are paid (weekly, bi-weekly, monthly, etc), whether employees are paid in arrears (often paid a few days after the end of the pay period) and how close the end of the pay period is to the end of the accounting period.
For the purposes of this forecast/module example, assume that the Salaries and Wages Payable will change in proportion to Revenue. Since, Salaries and Wages Payable will change in proportion to revenue, you will need to multiply the prior year’s Salary and Wages Payable by the change in revenue from prior year to current year.
For example, if last years revenue was 120 and this year’s revenue was 220, you would multiply last years salaries & wages payable by 220/120. - Alter P, E, & E - (Examples for altering listed below)
- new equipment could be purchased to replace older equipment.
- Second, as businesses are acquired it is anticipated that a portion of the acquisition cost is assigned to the value of the equipment of the acquired business.
- initial investments for new ventures/opening new locations.
- Depreciation- (rememer land doesn’t depreciate- also confirm what type of depreciation)
-
Borrowings & Interest - This causes a circulatory problem.
The circularity problem in forecasting interest and borrowings occurs when a company is relying on borrowings to finance the business activities. However, some companies have no borrowings and, hence, no interest expense. Some other companies do have some amount of borrowings but they are not relying on those borrowings to finance their operations.- Figure out Loans (hopefully, they are predictable like mortgages and just need rounding!) and add it to current portion of debts
- If the above is a payment on long-term debt, subtract that amount from long-term debt to receive new long-term debt amount.
- Edit Interest Expenses (check to see if it’s for the beginning or end of the year)
-
Income Tax Expense -
Income Tax Expenses = Tax Rate x Income Before Taxes -
Forcast Common Stock-
Common stock reflects the amount of funding raised by issuing shares of stock to individuals that will hold a stake in the company. We would generally expect common stock to stay the same from year to year, unless the business is planning on issuing more stock or buying back its own outstanding shares. -
Retained Earnings
How to Forcast Retained Earnings = Previous Retained Earnings + Current Year Net Income (Excluding the amounts to be distributed to shareholders) -
Solve for Cash or Borrowings!
We simply subtract the balances of the asset accounts that have been forecast from the total liabilities and equity in the forecast to arrive at the forecast for cash.
CASH = EQUITY + LIABILITIES - (Sum of all ASSETS that aren’t CASH)-
Note that in forecasts for businesses that finance their operations with borrowings:
- you will solve for borrowings as the plug and
- in forecasts for businesses that do not finance their operations with borrowings
- you will solve for another variable, such as cash, as the plug. In the case of a startup, it may be most appropriate to use equity as a plug, so the company can determine how much equity financing they will need to fund their operations.
-
Note that in forecasts for businesses that finance their operations with borrowings:
For Metropolis, sales & marketing expenses are expected to be 12% of revenue. With forecasted sales revenue of ₹220 for 2004, what will be the forecasted sales & marketing expenses for 2004?
₹10.2
₹12.1
₹22.2
₹26.4
₹26.4
Sales & marketing expenses are expected to be 12% of net sales. In order to calculate, multiply 2004 sales by 12%.
Calculation: (220.0 * 0.12) = 26.4
How to decide the level of detail for a pro-forma statement?
The level of detail and attention given to forecasting individual expense items will depend on the purpose of the forecast.
- If the forecaster is only interested in high-level general trends, then assumptions can be broad and details sparse.
- However, if the forecast will be used to decide whether to acquire a business, the forecast should be thorough and the details plentiful.
How to get the #’s in blue…
Remember: you should use formulas and linked cells whenever possible when using spreadsheets. The answers below reflect the use of the formulas shown.
2004: Administrative expenses are expected to be 8% of sales. In order to calculate, multiply this year’s sales by 8%.
Calculation: (220.0 * 0.08) = 17.6
Formula: =D6*0.08
2005: Administrative expenses are expected to be 8% of sales. In order to calculate, multiply this year’s sales by 8%.
Calculation: (387.5 * 0.08) = 31.0
Formula: =E6*0.08
2006: Administrative expenses are expected to be 8% of sales. In order to calculate, multiply this year’s sales by 8%.
Calculation: (611.3 * 0.08) = 48.9
Formula: =F6*0.08
Turnover
is an accounting term that calculates how quickly a business collects cash from accounts receivable or how fast the company sells its inventory. In the investment industry, turnover is defined as the percentage of a portfolio that is sold in a particular month or year.
2004: 60% of sales are on credit and accounts receivable are expected to turn over 6 times per year. In order to calculate, multiply this year’s sales by 60% to get sales on credit, and divide by the turnover of 6 to get the balance of accounts receivable at the end of the year.
Calculation: (220.0 * 0.6) / 6 = 22.0
Formula: =(D6*0.6)/6
2005: 60% of sales are on credit and accounts receivable are expected to turn over 6 times per year. In order to calculate, multiply this year’s sales by 60% to get sales on credit, and divide by the turnover of 6 to get the balance of accounts receivable at the end of the year.
Calculation: (387.5 * 0.6) / 6 = 38.8
Formula: =(E6*0.6)/6
2006: 60% of sales are on credit and accounts receivable are expected to turn over 6 times per year. In order to calculate, multiply this year’s sales by 60% to get sales on credit, and divide by the turnover of 6 to get the balance of accounts receivable at the end of the year.
Calculation: (611.3 * 0.6) / 6 = 61.1
Formula: =(F6*0.6)/6
2004: Days Purchases Outstanding is expected to remain constant at 40.6 days. Recall that Days Purchases Outstanding is calculated as (Accounts Payable)/(COGS/365). In order to calculate, we can rearrange this formula to solve for Accounts Payable by multiplying the Days Purchases Outstanding by (COGS/365).
Calculation: 40.6 * (99.0 / 365) = 11.0
Formula: =40.6*(D7/365)
2005: Days Purchases Outstanding is expected to remain constant at 40.6 days. Recall that Days Purchases Outstanding is calculated as (Accounts Payable)/(COGS/365). We can rearrange this formula to solve for Accounts Payable by multiplying the Days Purchases Outstanding by (COGS/365).
Calculation: 40.6 * (174.4 / 365) = 19.4
Formula: =40.6*(E7/365)
2006: Days Purchases Outstanding is expected to remain constant at 40.6 days. Recall that Days Purchases Outstanding is calculated as (Accounts Payable)/(COGS/365). We can rearrange this formula to solve for Accounts Payable by multiplying the Days Purchases Outstanding by (COGS/365).
Calculation: 40.6 * (275.1 / 365) = 30.6
Formula: =40.6*(F7/365)
Remember: you should use formulas and linked cells whenever possible when using spreadsheets. The answers below reflect the use of the formulas shown.
2004: Salaries and Wages Payable are expected to change proportionally to revenue. In order to calculate, multiply the prior year’s Salary and Wages Payable by the ratio of this year’s sales to the prior year’s sales.
Calculation: 0.2 * (220.0 / 120.0) = 0.4
Formula: =B34*(D6/B6)
2005: Salaries and Wages Payable are expected to change proportionally to revenue. In order to calculate, multiply the prior year’s Salary and Wages Payable by the ratio of this year’s sales to the prior year’s sales.
Calculation: 0.4 * (387.5 / 220.0) = 0.6
Formula: =D34*(E6/D6)
2006: Salaries and Wages Payable are expected to change proportionally to revenue. In order to calculate, multiply the prior year’s Salary and Wages Payable by the ratio of this year’s sales to the prior year’s sales.
Calculation: 0.6 * (611.3 / 387.5) = 1.0
Formula: =E34*(F6/E6)
2004: PP&E is comprised of three separate components: land, buildings, and equipment. Land is not depreciated. Depreciation expense for equipment is given, ₹16.4 million. Buildings with an original cost of ₹40 million are being depreciated straight-line over 40 years, so we can simply divide ₹40 million by 40 years to find the depreciation expense on the buildings.
Calculation: = 16.4 + (40 / 40) = 17.4
2005: PP&E is comprised of three separate components: land, buildings, and equipment. Land is not depreciated. Depreciation expense for equipment is given ₹19.1 million, Buildings with an original cost of ₹40 million are being depreciated straight-line over 40 years, so we can simply divide ₹40 million by 40 years to find the depreciation expense on the buildings.
Calculation: = 19.1 + (40 / 40) = 20.1
2006: PP&E is comprised of three separate components: land, buildings, and equipment. Land is not depreciated. Depreciation expense for equipment is given, ₹24.5 million. Buildings with an original cost of ₹40 million are being depreciated straight-line over 40 years, so we can simply divide ₹40 million by 40 years to find the depreciation expense on the buildings.
Calculation: = 24.5 + (40 / 40) = 25.5
Remember: you should use formulas and linked cells whenever possible when using spreadsheets. The answers below reflect the use of the formulas shown.
2004: Accumulated depreciation is calculated by adding this year’s depreciation expense to the prior year’s accumulated depreciation.
Calculation: (-91.2 - 17.4) = -108.6
Formula: =B29-D11
2005: Accumulated depreciation is calculated by adding this year’s depreciation expense to the prior year’s accumulated depreciation.
Calculation: (-108.6 - 20.1) = -128.6 (Note: due to the use of formulas and rounding, the answer here is -128.6)
Formula: =D29-E11
2006: Accumulated depreciation is calculated by adding this year’s depreciation expense to the prior year’s accumulated depreciation.
Calculation: (-128.6 - 25.5) = -154.1
Formula: =E29-F11
2004: The current portion of long term debt is forecast to be ₹1.03 million (rounded to 1.0). Recall that at December 31, 2004, the current portion of long term debt is equal to the amount expected to be paid in the coming year, 2005.
2005: The current portion of long term debt is forecast to be ₹1.08 million (rounded to 1.1). Recall that at December 31, 2005, the current portion of long term debt is equal to the amount expected to be paid in the coming year, 2006.
2006: The current portion of long term debt is forecast to be ₹1.135 million (rounded to 1.1). Recall that at December 31, 2006, the current portion of long term debt is equal to the amount expected to be paid in the coming year, 2007.
2004: Since no additional long term debt is expected to be acquired, long term debt is calculated simply as the prior year’s long term debt less this year’s current portion of long term debt.
Calculation: 31.4 - 1.0 = 30.4
Formula: =B37-D35
2005: Since no additional long term debt is expected to be acquired, long term debt is calculated simply as the prior year’s long term debt less this year’s current portion of long term debt.
Calculation: 30.4 - 1.1 = 29.3
Formula: =D37-E35
2006: Since no additional long term debt is expected to be acquired, long term debt is calculated simply as the prior year’s long term debt less this year’s current portion of long term debt.
Calculation: 29.3 - 1.1 = 28.2
Formula: =E37-F35
2004: Interest expense is forecast to be 5% of the total long term debt balance at the beginning of the year. In order to calculate, add the prior year’s current portion and non-current portion of long term debt, and multiply by 5%.
Calculation: (1.0 + 31.4) * 0.05 = 1.6
Formula: =(B35+B37)*0.05
2005: Interest expense is forecast to be 5% of the total long term debt balance at the beginning of the year. In order to calculate, add the prior year’s current portion and non-current portion of long term debt, and multiply by 5%.
Calculation: (1.0 + 30.4) * 0.05 = 1.6
Formula: =(D35+D37)*0.05
2006: Interest expense is forecast to be 5% of the total long term debt balance at the beginning of the year. In order to calculate, add the prior year’s current portion and non-current portion of long term debt, and multiply by 5%.
Calculation: (1.1 + 29.3) * 0.05 = 1.5
Formula: =(E35+E37)*0.05
What is the circularity issue with borrowings and interest?
Borrowings are used to determine the interest expense,
which impacts net income and retained earnings.
But retained earnings are used to calculate borrowings.
To resolve the circularity,
you will have to go through a couple of iterations
to get the balance sheet equation to balance.
COMMON STOCK
Common stock reflects the amount of funding raised by issuing shares of stock to individuals that will hold a stake in the company. We would generally expect common stock to stay the same from year to year, unless the business is planning on issuing more stock or buying back its own outstanding shares. In our example below, we’ll assume it is going to stay constant from one year to the next.
Define Retained Earnings and how to forcast them.
Retained earnings reflects the earnings that have been retained in the business to finance growth or future operations. For any given year, we can forecast retained earnings by adding the amount of prior year retained earnings plus current year net income, excluding amounts to be distributed to shareholders.
How to Forcast Retained Earnings = Previous Retained Earnings + Current Year Net Income (Excluding the amounts to be distributed to shareholders)
How To Solve for a Cash Forecast!
Is a business doesn’t use borrowings you’ll need to solve for cash?
- We simply subtract the balances of the asset accounts that have been forecast from the total liabilities and equity in the forecast to arrive at the forecast for cash.
CASH = EQUITY + LIABILITIES - (Sum of all ASSETS that aren’t CASH) - Note that in forecasts for businesses that finance their operations with borrowings:
- you will solve for borrowings as the plug and
- in forecasts for businesses that do not finance their operations with borrowings
- you will solve for another variable, such as cash, as the plug. In the case of a startup, it may be most appropriate to use equity as a plug, so the company can determine how much equity financing they will need to fund their operations.
Free Cash Flows. Define & List Equation
As with most financial analysis tools, free cash flows can be calculated in different ways depending on the purpose, the audience and the information available. In some cases, free cash flows are defined as operating cash flow - capital expenditures. Regardless of the precise methodology, free cash flows are intended to show the company’s normal ability to generate cash. In this course, we will use one of the most common methods to calculate free cash flows using the equation attached below.
- The first step in calculating free cash flows is to eliminate the impact of how the business or investment is financed. Free Cash Flows are normally calculated so they can be compared to some other alternative use of funds. In order to put the alternatives on a comparable basis they need to be financed on a comparable basis. The easiest way to do this is to assume that they are financed entirely by equity.
- Calculate:
- EBIT (by adding back the interest expense and income taxes expense lines to the net income)
- Then EBIAT (by multiplying 1- the tax rate * EBIT)
- Create a new row on the spreadsheet for depreciation
- Create a new row on the spreadsheet for Capital Expeditures
- Calculate Change in Networking Capital by:
- *CHANGE IN** NET WORKING CAPITAL =
- *NET WORKING** CAPITAL FOR CURRENT YEAR (which is NET WORKING CAPITAL = CURRENT ASSETS - CURRENT LIABILITIES) - NWC PREVIOUS YEAR (which is NET WORKING CAPITAL = CURRENT ASSETS - CURRENT LIABILITIES)
How to Calculate EBIAT
How to Calcuate: EBIAT, Earnings Before Interest After Taxes: we first calculate Earnings Before Interest and Taxes or EBIT for short. The information for calculating EBIT comes from the income statement.
- Starting with the bottom line, net income, we add back the interest expense and income taxes expense lines. The result is EBIT. Once we have EBIT, we can easily calculate EBIAT.
- To calculate EBIAT we multiply EBIT by 1 minus the tax rate. So at this stage, we have merely taken away the impact of interest from the income statement. We have re-applied the tax rate to the EBIT in arriving at EBIAT.
Which of the following statements are true? Select all that apply.
- EBIT equals EBIAT if there is no interest expense.
- EBIAT eliminates the impact of interest and taxes from the net income.
- EBIAT re-applies the tax rate to a pretax amount that excludes the cost of interest.
- The term Free Cash Flow means that no outside entity has any claim on these funds.
- EBIAT re-applies the tax rate to a pretax amount that excludes the cost of interest.
- This is the correct answer! This statement is true.
Current assets (excluding cash) less current liabilities equals Net Working Capital.
In this case, currents assets total $1,700 (accounts receivable and inventory) and current liabilities total $1,200.
The difference, NWC, is $500.
Change in Net Working Capital is calculated as the current year’s net of current assets (excluding cash) and current liabilities less the prior year’s net of current assets and current liabilities.
The Net Working Capital for 2007 was $1,400.
The Net Working Capital for 2006 was $1,325.
The difference, the change in NWC, is 75.
The correct answer is -$105,925.
The correct answer formula is:
=PV(E4,E5,E6)
Where # Periods (E5) is 15, and Pmt Amount (E6) is 12,000.
Notice that the PV formula makes the answer negative. This is because it represents the amount you would be willing to pay today to receive the given cash flows. You would be willing to pay this amount because it is equivalent to the amount you will receive, so the net amount you pay and receive will be zero.
So, if your discount rate is 7.5%, you would be willing to pay $105,925 for equipment that would provide this stream of rental income over the next 15 years.
What is The Gordon Growth Model?
It is not practical, and in some cases not even possible, to determine the cash flows for each year when the project can last a long time or even indefinitely. Fortunately, there are alternative ways to estimate
the value of a seemingly endless cash flow.
One example of a way to estimate the value of a seemingly endless stream of cash flows is the Gordon Growth Model.
- This model makes a major assumption that growth will remain steady indefinitely. As a result, this model should only be used once the cash flows are expected to stabilize. In periods of high growth or expected fluctuations, businesses should project the cash flows on a yearly basis.
- It is worth noticing, that since this equation gives us the present value of the cash flows as of the end of our forecast, we still need to discount the value back to the beginning of our forecast using the present value technique we introduced in the previous concept.
A company is considering investing in a new project with an infinite life. The estimated cash flows of the first three years of the project are shown in the attached photo.
After 2016, annual cash flow of the project is expected to stabilize at about 2% growth. The discount rate is 5%.
- What is the Terminal Value of the project on 1/1/2017?
- What is the Present Value of the project on 1/1/2014?
- The present value of $1 for 1 year at 5% is 0.95238
- The present value of $1 for 2 years at 5% is 0.90703
- The present value of $1 for 3 years at 5% is 0.86384
Solution to #1: Because cash flow is expected to grow at a stable rate of 2% after 2016, we can calculate the cash flow of 2017, the first period of stabilized period, as $2,000 * 1.02 = $2,040.
According to the Gordon Growth Model:
Terminal Value = CF / (r - g) = $2,040 / (5% - 2%) = $68,000
So the terminal value of the project on 1/1/17 is $68,000.
Solution: We have already calculated that the terminal value is $68,000. Now, we just need to find the present value of all cash flows, including the terminal value.
($1,000 * 0.95238) + ($1,500 * 0.90703) + ($2,000 * 0.86384) + ($68,000 * 0.86384) = $62,780
So the present value of the project on 1/1/14 is $62,780.
The correct answer is: $108,150
Because cash flow is expected to grow at a stable rate of 3% after 2018, the cash of 2019, the first period of stabilized period, is $4,200 * 1.03 = $4,326.
According to the Gordon Growth Model, Terminal Value = CF / (r - g) = $4,326 / (7% - 3%) = $108,150
The correct answer is $398,470.
Because cash flow is expected to grow at a stable rate of 1% after 2016, the cash of 2017, the first period of stabilized period, is $21,000 * 1.01 = $21,210.
According to the Gordon Growth Model, Terminal Value = CF / (r - g) = $21,210 / (6% - 1%) = $424,200
Present Value on 1/1/2014 = ($12,000 * 0.94340) + ($15,000 * 0.89000) + ($21,000 * 0.83962) + ($424,200 * 0.83962) = $398,470
The correct answer is $35.
The correct answer formula, which should be entered into cell E3 is the following:
=NPV(E2,B3:B10)+B2
There is one important thing to note about using the NPV formula. The formula itself does not include any cash flow at time 0 (today). Instead, the formula takes into account all future cash flows, and nets them back to the present value today, so if there are any cash flows at time 0 (today), those must be added or subtracted manually.
Because the NPV is positive, even though it is very small, it means that the sum of the discounted cash inflows is greater than the sum of the discounted cash outflows, so the project would yield a return greater than the discount rate.
The correct answer is: $30,979
NPV = (-$40,000) + ($12,000 * 4.91732) + ($18,000 * 0.66506) = $30,979
Or
NPV = (-$40,000) + ($12,000 * 5.58238) + ($6,000 * 0.66506) = $30,979
IRR
The IRR is the discount rate that sets the NPV of a project equal to zero.
The IRR allows us to see the percentage rate that will be earned for a given set of cash flows. This method incorporates the time value of money as the NPV does.
Calculate IRR: Using a Spreadsheet
The function in a spreadsheet is:
=IRR(values)
- Values is an array of numbers for which to calculate the internal rate of return. Values must contain at least one positive value and one negative value. The order of values must be in the order of cash flows.
The correct answer is 8.15%
The correct answer formula, which should be entered into cell E4 is the following:
=IRR(B2:B10)
The IRR formula is an iterative calculation that operationally uses a guess-and-check method to find the correct discount rate that sets the NPV equal to zero.
To test the solution, you can use the NPV formula and use the IRR as the discount rate to prove that the IRR indeed is the discount rate at which the NPV is equal to zero.
payback period
The payback period tells us how fast the investors can expect to have their money returned. This metric is more useful for someone who is concerned about limiting the downside potential
There is not a specific function in the spreadsheet, but we can use a spreadsheet to help with the calculation. The best way to calculate the payback period is to:
- calculate the cumulative cash flows.
- Another way to think of the payback period is that it is the “breakeven” point, or the point at which positive cash flows and negative cash flows incurred to date net to zero. By calculating the cumulative cash flows, it is easy to see when the cash flows flip from being negative to being positive. If it doesn’t flip exactly at a certain year, then you just need to find the portion of the following year that it takes to recover the remaining cash outlay.
Question: Suppose you are considering buying a machine that costs $7,000. It will generate revenues of $1,500 for the next 3 years, and then $1,000 for following 5 years. What is the payback period of this investment?
The correct answer is 5.5 years. This means it will take 5.5 years for the undiscounted cash flows related to the investment to net to zero, or in other words, it will take 5.5 years for the company to recover the amount of their initial investment.
The correct answer formula, which should be entered into cell F3 is the following:
=A7-(C7/B8)
Solution: To solve this problem, enter the following formula into cell F3:=A7-(C7/B8)
Note that this formula takes the number of years before cumulative cash flows flip from being negative to being positive, and then adds the fraction of the year it takes for the remaining cash outlay to be recovered in the following year.
The correct answer is: 5.5 years
Payback period is the period of time required to recover the cost of an investment. It ignores the time value of money.
In this example, the sum of net cash flows of the first 5 years is $144,000. Payback Period = 5 + ($155,000 - $144,000)/$22,000 = 5.5 years.
