Download Excel File: https://excelisfun.net/files/EMT1762.xlsx
See the algorithms for the Excel worksheet functions NPV and XNPV and see why XNPV removes more interest in the discounting process than NPV function.
Also, learn many cool worksheet formula tricks to make you work faster and more efficient.
Topics
(00:00) Introduction to NPV and XNPV and getting different answers.
(00:33) Cash Flows that you are NOT allowed to use to compare the two functions.
(00:56) Cash Flows that you ARE allowed to use to compare the two functions.
(01:15) How to use NPV function.
(01:48) How to use XNPV function
(02:02) Name cells using a keyboard shortcut: Create Name From Selections = Ctrl + Shift + F3.
(02:27) Calculate difference between net present value created by both functions using Defined Names.
(02:39) Capital Budgeting Cash Flow Analysis is all estimation.
(02:53) Algorithm for NPV function using an Old School Formula.
(03:14) RIGHT formula trick.
(03:45) Convert text number to number with math operation.
(04:05) Keyboard for SUM function: Alt + =
(04:10) Algorithm for XNPV function using a Dynamic Spilled Array Formula.
(05:17) Editing a Dynamic Spilled Array Formula.
(05:58) Three Benefits of Dynamic Spilled Array Formulas.
(06:18) Bonus Formula: Pinpoint why the functions and algorithms give a different answer: Leap year – just one extra day – affects the discounting process and cause formula to remove more interest for XNPV function as compared to NPV function.
(07:37) Default alignment helps us determine if year is a leap year.
(08:00) Conclusion and video links.