The general formula for Net Present Value (NPV) in a certain period is:
NPV(period) = PV(benefits, period) - PV(costs, period)
PV(value, period) = value/((1+ interest rate)^period)
The interest rate corresponds to your period (for example, 12% annually implies a 1% interest rate if your periods are months).
Period should be zero for the current period and this assumes that the value is at the beginning of the period.
NOTE: When the application computes the results, the application always uses full MONTHLY period amounts. Therefore, if you enter a lump sum or Cost Plan period amounts, the data is converted into daily amounts and multiplied by the number of days within the full specific month to determine the monthly amount.
The formula for NPV of a stream of benefits and costs is simply the above formula applied to all time periods.
The formula for ROI of a stream of benefits and costs is similar, where
ROI = NPV / PV(costs, period).
Note: The calculations cannot be modified.
Actual Cost - the actual cost is populated in planning if time transactions are posted to WIP. The transaction post to financial does the cost and rate calculation based on the matrix setup.
Budgeted NPV - The present value of the Budgeted Benefit distribution less the present value of the Budgeted Cost distribution; can be directly entered or system-calculated.
Forecasted NPV - The present value of the Forecasted Benefit distribution less the present value of the Forecasted Cost distribution; can be directly entered or system-calculated.
Calculate Capital Expressions - If checked, when form is submitted the ROI, NPV and Break-even values are calculated from the corresponding cost and benefit values using the configured Cost of Capital as the discount rate.
Budgeted ROI - The Budgeted NPV divided by the present value of the distribution of the Budgeted Cost; can be directly entered or system-calculated
Forecasted ROI - The Forecasted NPV divided by the present value of the distribution of the Forecasted Cost; can be directly entered or system-calculated.
Budgeted Break-Even - The end of the day on which the accumulated present value-adjusted budgeted benefits equal the accumulated present value-adjusted budgeted costs; can be directly entered or system-calculated.
Forecasted Break-Even - The end of the day on which the accumulated present value-adjusted forecasted benefits equal the accumulated present value-adjusted forecasted costs; can be directly entered or system-calculated.
Note: The code makes an explicit check that both revenue and cost are greater than 0.001; if either fails the test then NPV, ROI and Breakeven are explicitly set to NULL.
Reference the attached Microsoft Excel Workbook.
This workbook contains 3 spreadsheets where the user can enter numeric values into the formula and see the results as Clarity would calculate the values. The 3 spreadsheets allow the user to calculate 'Fully Overlapping Periods', 'Non-Overlapping Periods' and 'Partial Overlapping Periods'.
This attachment has been updated for examples computed in Clarity 12.1.
The first set of examples in this workbook reflects calculations using a Simple Budget amount that is evenly distributed for the duration of the number of months specified. The second set of examples reflects detailed calculations from Detailed Financial Plans where the amounts vary from month to month. Each worksheet has screenshots to help illustrate the example.
SB = Simple Budget Calculations - where the total Cost or Revenue amount is distributed evenly over the date range
SB-Fully Overlapping Periods
SB-Partial Overlapping Periods
SB-Example 3 Years
SB-For Testing -- Use this worksheet to enter your own periods and data values
DET = Detailed Financial Cost Plan with linked Detailed Benefit Plan - where each monthly period may have different amounts of Cost or Revenue
DET-Fully Overlapping Periods
DET-Partial Overlapping Periods
DET-For Testing -- Use this worksheet ot enter your own periods and data values