1. J

    NPV for annual operating costs and reoccurring fixed cost every X years

    Hi all, I am developing a forecast of costs using the NPV formula which include operating and fixed costs however, the fixed costs reoccur every X years (depending on the type of equipment). The model looks at costs of switching from equipment 1 to equipment 2. Equipment 1 only has operating...
  2. S

    Determining Purchase Price on Real Estate Transaction using Goal Seek, NPV, and IRR

    I work in commercial real estate and we are trying to back into a purchase price by determining what initial outlay gets our NPV to 0 and IRR to 30%. Essentially, what acquisition price gets us to a 30% IRR. We're looking at this over a three year period. For purposes of simplicity, say I can...
  3. L

    NPV without repetitive cash flows

    Hi, Is there any way get NPV of uneven cash flows, without listing all cash flows? I'm hoping to replicate the Financial Calculators CF function, for instance BA II plus has CF function. You don't have to list all cash flows, you just point out how many time each cash flow should be paid out...
  4. C

    NPV with a stepped rental

    Hi I'm trying to work out the NPV of a simple repayment profile that has a stepped rental i.e X months at $100 followed by Y months at $1000 I can do this by creating lists but I want to develop a calculator where I can solve the NPV by having X & Y as variable numbers. I have other tools I...
  5. O

    NPV Formula to Cut Off Values after Economic Limit

    Thanks in advance for any suggestions, and I will provide feedback on any proposed solutions. How do I modify the following formula to cut the columns off after the economic limit and beyond (i.e. not include them in the NPV Formula). = NPV( ( 1 + $B$1 )^(1/12)-1, $L$198:$ATO$198) Cell A1 has...
  6. J

    Nest NPV function within PMT formula as the argument for "PV" .

    I've been given a particularly diabolical challenge, and for the life of me, I cannot make it work. The challenge is to execute within a single cell a formula that will calculate "EAC: equivalent annual cost" within a single cell. There are two distinct steps in the calculation; first, one...
  7. H

    Reverse-engineer NPV to get rate

    Hi I can't find any information on how to do this other than through some sort of goal-seek. Ideally I'd like a formula-based solution, if one exists. Basically I need to 'reverse-engineer' the NPV calculation to find the rate. I've illustrated my problem in the image below (I'm trying to find...
  8. M

    NPV & IRR with nested Offset Function help required

    I am attempting to understand an NPV/IRR calculation using nested Offset from 2017 to 2027. The formulae are as follows: NPV(C15,OFFSET(D39,0,0,1,C16-1))+C39 Where C15 = WACC, D39 = 2018 net annual benefits, C39 = 2017 net annual benefits & C16 = valuation years IRR(OFFSET(C39,0,0,1,C16))...
  9. V

    NPV Auto-Formatting

    Hello, Every time I make a change within the formula or activate a cell that has a PV, NPV, IRR, etc. function in it, the cell automatically reformats to currency when I press enter. Would anyone know how to turn the auto-formatting feature off? Thank you for your help.
  10. C

    Dynamic Periods NPV

    Is it possible to calculate a Future Value that has an NPV of 0 without using goal seek or VBA? I have an investment in stock quantity (D3) and price (D4) to calculate a cash outflow (D5). I have a return rate (D8) of 10% and a holding period (D7) in months and there are no other cash inflows...
  11. T

    Can I set a range in a formula that based on a variable in another cell?

    I have a table of annual payments and I want to calculate the net present value of future payments (NPV) as of the “nth” year from now, where “n” is a number that I enter into another cell. I’m trying to calculate a payoff amount at some date in the future. Let’s say I have 10 years of...
  12. J

    NPV Calculator

    Please be kind... i'm a rookie user. I'm trying to create a space planning and capital estimating tool that has several inputs. The one I'm struggling with is calculating NPV for a variety of scenarios. More specifically, I'd like a user to be able to enter a particular number of years on a...
  13. C

    Net Present Value Template with Weekly Payments

    I am trying to put together a template showing the net present value of three years' of weekly payments. Each payment is $5,000. I am trying to compare that to lump sum payment at the end of each quarter for the three years and also to a lump sum payment at the end of each of the three years...
  14. V

    Have to repeatedly enter same value into cell to manually force convergence...

    I have a custom function to calculate NPV, which is called every time one of several cells changes (to avoid circular references, since the user doesn't want to use iterative calculation). The initial result is always slightly off; however, if I reenter the same value in any of the target cells...
  15. T

    Help with Lease Amortization Schedule

    I am trying to amortize a lease liability schedule to zero using an assumed rate of 5%. I realize the implicit rate is different but I've discounted the cash flows at 5% and would like the amortization to zero out starting with the current lease liability derived using XNPV. I get close to...
  16. R

    HELP! VBA Macro Deleting Formula From Cells

    I am trying to run a breakeven analysis using a Goal Seek. The Goal Seek looks at the NPV, and should put in a new price to change the NPV to 0. However, I would like for the NPV cell to retain the formula rather than change to the hard-coded number 0 every time I run it. Is there something...
  17. A

    Positive NPV yet IRR < WACC

    Hi Guys, I'm having an odd result in excel. I have a project with a positive NPV but the IRR excel is giving me (both IRR and XIRR formulas) is LESS than the WACC. It does not make sense, if NPV > 0 then IRR should also be > WACC. The CF's in the project do change signs in between and go from...
  18. X

    Calculating the net present value with semi annual costs

    Hey, i am a student at the university of utrecht and i have calculated the net present value of several projects successfully and have been asked to do so again. I realised that i keep rebuilding the excel sheet i use to do that for one reason, i cant find a way to add the initial investment...
  19. H

    NPV Question

    Hi All, I have an excel formula i am trying to solve for. I have a table of loan payments that layout a monthly payment which looks like this <colgroup><col span="3"><col><col><col><col><col span="2"></colgroup><tbody> Start End Principal Payment Interest Principal Principal...
  20. E

    NPV formula error?

    Hi, I'm taking a basic course in accounting and for some reason Excel doesn't offer me the correct answer for Net Present Value: when I insert the following data and the following formula, it gives the result as -222.91, although I know from the problem's answer and by using another calculator...
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back