
Search Excel Forum Posts, Tutorials, Macros, Tips, and More
Hello,
I'm trying to find the beakeven point for variable cash flows.
Cash Flows per year
Fixed Cost
1 yr
2 yr
3 yr
4 yr
5 yr
6 yr
7 yr
22,000,000
5,000,000
5,500,000
3,800,000
7,500,000
1,200,000
4,000,000
3,500,000
I know my breakeven point is around 4 years, but is there a formula I can use to calculate this?
I will be using this formula in a template where the fixed cost and cash flows will be changing.
My goal is to have the breakeven point calculated no mater what the Fixed Cost and Cash Flows display.
Similar Excel Video Tutorials
Point Estimates & Sample Error
 Topics for: 1.See how to take a sample from a data set using the INT, RAND, and VLOOKUP functions 2.Calculate our Point estimates: Mean, Sta ...
PMT function 5 Examples
 See 5 examples for the PMT function: 1.Monthly loan payment at the end of each month from the borrowers point of view 2.Monthly loan payment ...
OFFSET Function Dynamic Range
 See how to use the OFFSET function for two different types of dynamic ranges for a chart: 1)Dynamic Range that adds latest records 2)Dynamic ...
Similar Topics
Dear Smartest Excelers In The World,
I am trying to create a formula that will calculate the number of years it takes to recoup the initial cost of a project. For example, if the project cost $1000 at year 0 and the cash flows in year 1 & 2 are $500, the payback in years would be 2. If the cash flows were $500 in year 1, $400 in year 2, and $500 in year 3, the project would pay back in 2.2 years = 2 + 100/500. I have created this formula:
W
X
Y
3
Cash Flows
Cumulative Cash Flows
4
Year 0
1,000
1,000
5
Year 1
500
500
6
Year 2
400
100
7
Year 3
500
400
8
Year 4
200
600
9
Year 5
500
1,100
10
Year 6
0
1,100
11
Year 7
0
1,100
12
13
Payback In Years
2.2
Worksheet Formulas
Cell
Formula
Y13
=COUNTIF( Y5:Y11,"
Hello friend! Please take a look to see if you could help.
I have an annual cash flow forecast as follows:
Total 67,247,657
2011 (16,897,525)
2012 1,785,783
2013 2,246,005
2014 2,639,227
2015 1,971,116
2016 2,955,283
2017 3,159,493
2018 2,806,202
2019 3,476,776
2020 3,646,009
2021 3,822,240
2022 3,157,590
2023 4,191,510
2024 48,287,946
The IRR calculated based on these cash flows is $19.22%. Then I broke these annual cash flows into quarterly cash flows by dividing each annual cash flow by 4 assuming cash inflows and outflows occur at the end of each quarter, and used the XIRR function to calculate the IRR based on the quarterly cash flows. The annualized quarterly IRR (18.93%) is surprisingly lower than the IRR based on the annual cash flows.
I thought the IRR calculated based on the quarterly cash flows should be higher than that calculated based on the annual cash flows because the quarterly cash flows are in and out throughout the year, rather than at the end of the year.
Can anyone please kindly help? The only reason I could think of is that the time value of money makes the cash outflow upfront matters most. In the annual cash flow schedule, the cash outflow occurs at the end of the year, while in the quarterly cash flow schedule, the cash outflow occur throughout the year. Also, the amount of the upfront cash outflow is significant.
Thanks in advance.
Megan
Hello friend! Please take a look to see if you could help.
I have an annual cash flow forecast as follows:
Total
2011
2012
2013
2014
2015
67,247,657
(16,897,525)
1,785,783
2,246,005
2,639,227
1,971,116
2016
2017
2018
2019
2020
2,955,283
3,159,493
2,806,202
3,476,776
3,646,009
2021
2022
2023
2024
3,822,240
3,157,590
4,191,510
48,287,946
The IRR calculated based on these cash flows is $19.22%. Then I broke these annual cash flows into quarterly cash flows by dividing each annual cash flow by 4 assuming cash inflows and outflows occur at the end of each quarter, and used the XIRR function to calculate the IRR based on the quarterly cash flows. The annualized quarterly IRR (18.93%) is surprisingly lower than the IRR based on the annual cash flows.
I thought the IRR calculated based on the quarterly cash flows should be higher than that calculated based on the annual cash flows because the quarterly cash flows are in and out throughout the year, rather than at the end of the year.
Can anyone please kindly help? The only reason I could think of is that the time value of money makes the cash outflow upfront matters most. In the annual cash flow schedule, the cash outflow occurs at the end of the year, while in the quarterly cash flow schedule, the cash outflow occur throughout the year. Also, the amount of the upfront cash outflow is significant.
Thanks in advance.
Megan
I have a 70 year series of interest rates (not a flat curve) and a corresponding 70 year series of cash flows. I'd like to get the rolling NPV of this series of cash flows, with each cash flow discounted at the given fixed rate for its corresponding year to the npv year I'm trying to solve for, and past cash flows dropping out as they occur. I know I could do this with 70 different columns, but would ideally like to figure out a formula to do it in one. Any ideas would be greatly appreciated.
Thanks so much.
Don456
I have a series of monthly cash flows, less than 1 year in total duration, that I want to calculate the annual IRR for. Using XIRR seems to give me a pretty substantial IRR. I'm questioning whether it's valid to use XIRR when the cash flows are < 1 year. Possibly, XIRR has more utility with yearly cash flows, or at least future cash flows spanning multiple years? As an example, the cash flows below would be an example of what i might have. I know the actual date of the cash flow will impact the IRR, but i'm putting in a first of month date for ease. Is XIRR still the appropriate method to calculate the annual IRR? I'm fairly certain calculating the periodic IRR and multiplying by 12 is inappropriate, but maybe you can tell me.
5/1/2010  947
6/1/2010  +325
7/1/2010  +325
8/1/2010  +325
9/1/2010  +325
There are 2 parts to this question:
1. If you take the following set of cash flows below over, say, a 48 month period, and then calculate a simple IRR equation as =IRR(stream of cash flows), you get a #NUM error message. Why is that? It appears that once you hit month 28 with the example of 132,000 returned, the error message appears. I understood the error msg to appear for either infinite or uncalculable returns  but this stumps me. Why the "magic" 28 threshold?
2. For the financially literate, what is a more accurate measure of returns: the simple IRR function =IRR(cash:flows) or =(1+IRR(cash:flows))^(periods/year)1? A friend formerly at Bain and Goldman shared the latter with me, which, if you compare it with the simple IRR function, usually differs by 1% or less on average for any given set of cash flows.
Month
Cash Flows
0
($100,250)
1
$0
2
$0
3
$0
4
$0
5
$0
6
$0
7
$0
8
$0
9
$0
10
$0
11
$0
12
$0
13
$0
14
$0
15
$0
16
$0
17
$0
18
$0
19
$0
20
$0
21
$0
22
$0
23
$0
24
$0
25
$0
26
$0
27
$0
28
$132,000
29
$0
30
$0
31
$0
32
$0
33
$0
34
$0
35
$0
36
$0
37
$0
38
$0
39
$0
40
$0
41
$0
42
$0
43
$0
44
$0
45
$0
46
$0
47
$0
48
$0
Hi,
I'm new to excel and have been asked to model a rather complex investment structure. If anyone could help me with this I would be forever grateful. The cash flows are distributed between the limited partner (LP) and the managing partner (MP). There are three hurdles though with different divisions of cash for each. The structure is as follows
For cash flows generating an IRR less than 15%
LP gets 75%
MP gets 25%
For cash flows generating between IRR 15%20%
LP gets 50%
MP gets 50%
For Revenue reaching over 20%
LP gets 25%
MP gets 75%
So I have my NPV cash flows laid out and have calculated the IRR for each year but can't visualize how to calculate the relevant sums of money for each cash flow. For example, the amount of cash that generates a 15% IRR in year 3 will be different than the amount of revenue that generates a 15% IRR in year 4.
So say there is cash flow for a year that generates a 30% IRR. I would need to first split the cash into three separate sums. The first would be the cash that generates an IRR of up to 15%, then another sum of the cash on top of that that generates between 1520% IRR, then all the cash over 20%. The sum of these three chunks will equal the total cash flow for given year. Then I can apply the relevant structure to each cash sum (LP gets 75% of first sum, 50% of second sum, and 25% of third sum)
I need to split up the cash flows into the three different groups based on the hurdles so I can apply the correct structure to the relevant part of the cash flow.
Can I get excel to spit out the relevant sums of money for each hurdle level for each year?
Sorry if this is not clear, I am completely new to this and will elaborate if need be.
Thanks,
Colin
I have a series of hypothetical cash flows from D3:Y3. In cell B11, I have a list representing possible start dates, eg. "Q2Y1" represents second quarter of year 1, etc... D9:W9 has 20 quarters laid out in this format (i used this row to create my list in B11).
My goal was to have the cash flows start at the date I chose in B11 and follow the typical pattern in row 3 after the start date. To do this I used the following formula in cell D11 and copied it accross to W11 (NB: Column C is blank):
=IF($B11=D$9,OFFSET($C$3,,(MATCH($B11,$D$9:$W$9,0)
COUNTA($C$9:C$9))),IF(C110,INDEX($D$3:$Y$3,,SUMPRODUCT(($C$11:C110))+1),0))
This formula worked well, except for the fact that I cannot have any cash flows equal to zero (as a temporary fix, made 0 cash flows equal to 0.0001 instead).
But is there a more efficient / elegant formula that can accomplish my goal of having an entire cash flow pattern move according to the starting point i choose?
I am trying to to calculate the XNPV of a stream of cash flows. The problem
is that when I run differet scenarios through my model the cash flows are in
different years each time. Is there any way that I can get excel to
automatically calculate the XNPV of cash flows for only the years where my
cash flows are greater than zero or where my XNPV Dates are greater than
zero? Thanks in advance for your help!
I calculated a XNPV on a series of cash flows. The cash flow in the first 27
years is zero and then there are positive cash flows for another 25 years.
The calculation read the first positive cash flow as if it were at time zero
instead of discounting it back 27 years. I ended up just using a PV, but
would like to figure out how to use the XNPV function for this series of cash
flows. Thanks for any ideas.
Jim
Hey 
I am working on a real esate development/ waterfall model and need to calculate an IRR based on monthly cash flows. The cash flows are irregular and assumed to be recognized at the end of each month over a year term. Is the XIRR function the way to go? And do I need to divide my hurdle rate by 12? Thanks.
I have the following cash flows, each discounted at its corresponding discount rate, which gives me a 768.555 NPB.
Cash Flow Yrs Discount Rate Present Value
21.605 0,1 2,00% 21.562
21.605 0,6 2,12% 21.335
21.605 1,1 1,90% 21.161
723.605 1,6 1,69% 704.497
TOTAL 768.555
What i want to do is calculate the IRR which returns this present value given the cash flows.
What i've done is constuct the following cash flow
768.555
21.605
21.605
21.605
723.605
Which corresponds to the estimated present value as an initial investment and then consider the other cash flows. Then i use excel IRR function which returns 0,67%. But if i discount each cash flow at that rate it doesnt return the calculated NPV (768.555). Using goal seek i get 0,99%. What's the problem, Is there someway to get to this yield without using goal seek?
Hi,
I have some columns and rows of cash flows, so from columns A to F I have Cashflow 1, Cashflow2, ... Cashflow 6, and down to row 100 say. I would like to sum these cash flows, say in column Z. The problem is in later datasets the number of columns of cash flows could change, it could only be columns A to C with Cashflow 1, Cashflow 2, Cashflow 3 or it could be columns A to K with 11 cashflows, etc. How would I use an offset match and sum to sum the whole row of cash flows given that the header has the word "Cashflow" in it?
Thanks
not sure what the best way to phrase my question is so I'll just jump right into an explanation of what I'm trying to accomplish.
Given a series of dates (column a), a series of incoming cash flows (column b), and a hurdle amount, I'm looking for a formula that will tell me what date, in the series, did the cash flows meet or exceed the hurdle rate. For example if 12 months of cash flows gave me a grand total of $1000, what formula would tell me in which particular month did the total cash flows received to date exceeded $600. I'm needing to do this with hundreds of cash flows and hurdle rates, so I think that creating dynamic ranges or inserting additional columns would be too labor intensive. I'm sure that there is an extremely easy way to do this, unfortunately my mind has stopped working. thanks
I'm trying to find the present value of a series of all different cash flows but I want to use a series of rates to discount these cash flows instead of one fixed rate. Is there a function in Excel that will do this? Thanks.
Hi Excel genuises
I am trying to calculate a breakeven point with total fixed costs and variable number of people. With the detail below how is it possible to calculate a breakeven number of people and the value and then the number of people and value to show 15% margin?
REVENUE
Package price
$ 6.99
per person
Our revenue
$ 3.50
per person
COST OF SALES
Cost
$ 1.75
per person
Net Revenue
$ 1.75
per person
Total cost
Cost 1
(2084.00)
Cost 2
(54.50)
TOTAL COS
(2140.25)
Overhead
(4122.00)
CAPEX
(103.00)
TOTAL COSTS
(6365.25)
I am trying to figure out a formula that will return a cash flow that will result in a specific IRR target. Example:
Investor contributes $1,000,000 in year 0
Investor earns 9% compounded annually on their money (paid as cash flows allow, otherwise accrued)
Payments of cash flow are made as follows:
First to Investor to satisfy 9% preferred return
Second to Investor to repay any accrued preferred return
Third to repay Investor's initial contribution
Fourth 70/30 (Investor/partner) until Investor receives 20% IRR
Fifth 50/50
Because the cash flows don't support the 9% in the early years, the cash flows aren't the same year over year and thus I am getting hung up on using the FV calculation to back into the number required to get my Investor to a 20% return.
Is there a way to solve for a Future Value when the cash flows aren't even every year?
Hello all,
I am trying to calculate the NPV of a series of cash flows that are in one row (say 30 columns) However, the first cash flow may not actually occur in the first column...it may be in the second, third, fourth or later based on a date entered by the user. Any ideas on how to make the NPV funtion look for the first nonzero number in the row and then take the NPV of all the cash flows after that?
Thanks for your help
I need to use a cell reference to determine whether to include another cell in a formula. Specifically, I have a series of cash flows and I want to test the effect of using different loan durations against those cash flows based on the average cash flow for a given loan duration.
So I need to figure out how to use the cell containing the loan duration to cause the formula determining the average of the cash flows to only include cash flows for the years of the loan duration.
Hi
I have put together some breakeven analysis with a small P&L but as the breakeven number of sales and the P&L is dependant on the inputs the numbers do not remain constant even when formularized.
Is there a way (macro perhaps?) that I can have the breakeven number of sales AND the P&L change depending on the inputs as per below?
Many thanks
Inputs
Proposed price
$6.99
Revenue share
50%
New Revenue
$3.50
Revenue share to X
50%
X share
$1.75
Fixed cost
$6,471.00
Variable cost
$1
Breakeven P&L
REVENUE
Package price
60,511.42
Revenue
30,255.71
COST OF SALES
Revenue share
15,127.86
Variable cost per sale
8,656.86
Fixed cost
6,471.00
TOTAL COST OF SALES
30,255.71
Margin
0.00
Breakeven
%
Number of sales
0
8,657
15
9,955
I have a stream of cash flows and corresponding dates that I am calculating an XNPV for... I have the model fully automated to a few inputs can change everything...
However, I can't get the XNPV calculation to automatically change it's parameters as the cash flows and dates change... Is there anyway to do this?
Basically, I'm trying to find a way to have the XNPV function search for the first and last date with a cash flow and reset itself to calculate for those new cash flows.
I have a stream of cash flows and corresponding dates that I am calculating an XNPV for... I have the model fully automated to a few inputs can change everything...
However, I can't get the XNPV calculation to automatically change it's parameters as the cash flows and dates change... Is there anyway to do this?
Basically, I'm trying to find a way to have the XNPV function search for the first and last date with a cash flow and reset itself to calculate for those new cash flows.
Here is what I am trying to do: in cell A1 I have the number of cash flows (ie. 5) in cell B1 I have the IRR formula in cells C1 thru Z1 I have the cash flows. I need the IRR formula in Cell B1 to look at cell A1 to select the appropriate number of cashflows apon which to base the calculation. The references in the IRR calculation will change based on the number of cash flows in cell A1. I would like to copy this formula down the sheet for many cash flow senarios...
Cell A1: Value is 5
Cell B1: formula is IRR(C1:G1,)
Cells C1 thru G1: Cash flows
Thanks
When I have quarterly cash flows, I get an IRR, I annualize it ((1+IRR)^4)1, and I get the same (or close enough) result using XIRR and the dates. (I like to verify when the IRR seems unusual to me). Here's the dumb part of the question  I used sumif to determine my total cash flows for each year, and then calculated my IRR (& XIRR) over that period. (Exact same cash flows, only expressed annually instead of quarterly) Why would the answer be so significantly different?
Feeling kinda dumb today . . . . sorry.
Hi all,
I am trying to calculate the payback period for a series of cash flows. I have 5 years of cumulative cash flows from cells b24:f24 with the first year's cash flow being negative.
What is the right formula for calculating the payback period?
B24: 94,352
C24: 97,944
D24: 286,081
E24: 469,891
F24: 649,201
Thanks in advance!

