Power Pivot Principles: The A to Z of DAX Functions – FV

In our long-established Power Pivot Principles articles, we continue our series on the A to Z of Data Analysis eXpression (DAX) functions.  This week, we look at FV. 

The FV function

The FV functionis one of the financial functions which use to calculate future value of an investment have constant interest rate or payment.  It has the following syntax:

• rate: this is required and represents the interest rate per period
• nper: this is also required and represents the total number of payment periods
• pmt: this is also required and represents the payment made each period.  This pmt cannot change over the life of annuity.  Typically this pmt also includes principal and interest but no other fees or taxes
• pv: this is optional and represents the present value or the value of all the future payments and future value worth right now.  If this argument is omitted, it will assume to be BLANK
• type: this is optional and can be either zero [0] or one [1] can be entered in this argument.  If zero [0] is entered here this mean the payments are due at the end of a period (regular annuity) and if one [1] is entered here this mean the payments are due at the beginning of a period (annuity due).

It should be noted that:

• we need to make sure that the rate and the nper input are adjusted according to the period.  For example, if we make a quarterly payment on a 20-year loan at 12 percent annual interest.  The rate and the nper will be0.12/12 and 20*12 respectively.  If we make the annual payment then the rate will be 12% and nper will be 20
• for all arguments dealing with cash such as pv and pmt, a positive number represents a cash inflow, which is cash you receive, and a negative number represents the cash outflow, which is cash you pay out
• for the type argument, it will round the number to the nearest integer
• an error will be returned if nper is strictly less than one [1]
• this function is not supported for use in DirectQuery mode when used in calculated columns or row-level security (RLS) rules.

Let’s consider the following example where we making a saving plan where we will save $500 every year from now on till 20-years later, we are optimistic about the economy and it will be stable for 20-year at 12% annual interest rates.  We want to show a single value, therefore to make it easier to view, we will write the following DAX measure in the Power BI Desktop application :

We see that after 20 years, saving $500, we will have:

The variable _frequency helps us to deal with the adjustments from year to the payment period.  If we want to make the saving every month we can adjust this variable to 12:

After 20 years, saving $500 every month, we will make:

Come back next week for our next post on Power Pivot in the Blog section.  In the meantime, please remember we have training in Power Pivot which you can find out more about here.  If you wish to catch up on past articles in the meantime, you can find all of our Past Power Pivot blogs here.