FV Function - Different compounding frequency and contribution frequency

[Anonymous]

Hi there,

I am trying to implement the FV function as part of my dashboard calculator. I am needing to provide the user the flexibility to choose different interest compounding frequencies (eg. annually/monthly), whilst also allowing them to select their regular contribution frequency (e.g. annually/monthly). 

I have tried the following (refer below), but I can't quite get it to work. Any help would be appreciated. I imagine that I need integrated the frequencies of compound interest and contributions wrong in the below code. 

Expected Return w/ Regular Contributions = 
var n = // Frequency of compound
IF(
    SELECTEDVALUE('Compound Frequency'[Compound Frequency])="Annually",
    1,
    IF(
        SELECTEDVALUE('Compound Frequency'[Compound Frequency])="Monthly",
        12)
)
var m = // Frequency of contribution annually
IF(
    SELECTEDVALUE('Contribution Frequency'[Contrib Freq])="Annually",
    1,
    IF(
        SELECTEDVALUE('Contribution Frequency'[Contrib Freq])="Monthly",
        12
    )
) 
var r = 'Slider Annual Rate of Return'[Annual Rate of Return Value]
var P = 'Slider PV'[PV Value]
var t = (MAX(DatesTBL[Year]) - YEAR(TODAY()))*n
var PMT_ = 'Slider PMT'[PMT Value]*m/n

RETURN
if (
    t>=0,
    FV(r/n,t,-PMT_,-P,0),
    blank()
)

[OwenAuger · Accepted solution]

You're welcome @Anonymous ! 🙂

Example from  your post

Looking at your example first of all (n=1, m=12) my sample measure above would almost do what you described:

• The initial PV of $1,000 and the first 11 contributions (totalling 11* $100 = $1,100) would have been in the account for a nonzero fraction of a year.
• These partial periods are rounded up and treated as one year, so at the end of the year the interest is calculated as 5% * (1,000 + 1,100) = $105.
• The 12th contribution is made at the very end of the year (i.e. when there is zero time remaining), and, by the convention set up in the measure, no interest applies to that contribution (in that first year).

However, the compounding logic can be customized however you like, e.g. you could include all 12 Contributions in the interest calculation.

Explaining the measure

Just to explain the logic of the measure a bit more, it:

1. Creates an index for each contribution, with Contribution 0 being the PV and the rest being PMT.
2. For each Contribution, determines how many periods it should be compounded for (RemainingCompoundCount). In this example, it is defined as the number of remaining compounding periods rounded up to the nearest integer:
   CEILING ( n * ( T * m - i ) / m, 1 )
3. Calculates the compounded future value of the current contribution (ContributionFV).
4. Sum all the ContributionFV values to give the final result.

The key thing that you would likely want to customize would be point 2, the number of periods that a given contribution should be compounded for.

In the above measure, the expression

CEILING ( n * ( T * m - i ) / m, 1 )

calculates the number of compounding periods remaining after Contribution number i, rounded up.

If we wanted to allow fractional periods, we could use:

n * ( T * m - i ) / m

Or we could use any arbitrary expression to apply any other special logic.

Rewritten Measure

I have rewritten the measure a bit with some better variable names, with an additional variable CompoundingOption defined at the start, which is used later on to calculate CompoundPeriods.

Expected Return w/ Regular Contributions =
-- COMPOUNDING OPTION
-- Option 1: Each Contribution is compounded a whole number of periods,
-- with partial periods rounded up.
-- Option 2: Each Contribution is compounded fractional number of periods (geometric)
VAR CompoundingOption = 1
VAR n =
    // Frequency of compound
    SWITCH (
        SELECTEDVALUE ( 'Compound Frequency'[Compound Frequency] ),
        "Annually", 1,
        "Monthly", 12
    )
VAR m =
    // Frequency of contribution annually
    SWITCH (
        SELECTEDVALUE ( 'Contribution Frequency'[Contrib Freq] ),
        "Annually", 1,
        "Monthly", 12
    )
VAR r = [Annual Rate of Return Value]
VAR P = [PV Value]
VAR t =
    ( MAX ( DatesTBL[Year] ) - YEAR ( TODAY () ) ) // Expressed in years
VAR PMT_ = [PMT Value] // Unadjusted value of each Contribution
VAR r_adj = r / n
-- Create list of index values for each Contribution, to be iterated over by SUMX
-- When @ContributionIndex=0,   time = 0
-- When @ContributionIndex=T*m, time = T
VAR Index =
    SELECTCOLUMNS ( GENERATESERIES ( 0, T * m ), "@ContributionIndex", [Value] )
VAR Result =
    SUMX (
        Index,
        VAR i = [@ContributionIndex]
        VAR Contribution =
            IF ( i = 0, P, PMT_ )
        VAR CompoundPeriods =
            SWITCH (
                CompoundingOption,
                1, CEILING ( n * ( T * m - i ) / m, 1 ),
                2, n * ( T * m - i ) / m
            )
        VAR CompoundFactor = ( 1 + R_adj ) ^ CompoundPeriods
        VAR ContributionFV = Contribution * CompoundFactor
        RETURN
            ContributionFV
    )
RETURN
    Result

I have attached a couple of examples in Excel that I put together to test the calculations.

Also, I created a DAX query on DAX.do where you can plug in different parameters and see the table created during the calculation and the result itself.

https://dax.do/sybSoIWocVhgAH/

As I say, you can customize this however you like and hopefully it's of some use.

Regards,

Owen

[Anonymous]

@OwenAuger 

Thank you so much for sharing your wealth of knowledge! You have really gone above and beyond with your explanation. I never expected such a detailed response from an online forum.

The Dax.do looks like a very useful tool that I will also look into and learn from this example.

Thanks again!!!

[OwenAuger]

Hi @Anonymous 

Interesting 🙂

Using the FV function, future value can only be calculated assuming that contributions and compounding occur together (at the end of each period with the last argument of FV set to 0).

Your current measure effectively adjusts the contribution frequency to match compounding frequency.

Do you want to calculate FV more precisely, taking into account the different compounding and contribution frequencies?

If so, I think we would have to construct a cashflow table and do some calculations based on that, which I can try to help with.

Regards,

Owen

[Anonymous]

Hi @OwenAuger ,

Thank you for your reply. 

Using the FV function, future value can only be calculated assuming that contributions and compounding occur together (at the end of each period with the last argument of FV set to 0).
Does this mean that a custom measure to compute the future value would be required? As you mentioned, I have tried to manipulate the parameters going into the FV function to make it flexible for dissimilar frequencies for the contribution and compoudning interest. 

Your current measure effectively adjusts the contribution frequency to match compounding frequency.
My thought process with this was that the contribution would have only have interest apply based on the compounding frequency. (e.g. If contributions occured monthly with annual compounding interest, the interest rate would only be applied to the sum of the 12 month's of contributions made). I suspect I have oversimplified the problem. 

Do you want to calculate FV more precisely, taking into account the different compounding and contribution frequencies?

Yes, I would appreciate any guidance to achieve this. As a reference, I am trying to achieve something similar to this calcualator online. Compound Interest Calculator - Daily, Monthly, Yearly Compounding (thecalculatorsite.com). I will eventually want to incorporate more optional frequencies such as daily/quarterly/semi-annually etc. 

I have also tried searching for a modified version of the FV formuala below that allows varying frequencies between contributions and compounding interest (without any luck so far)

[OwenAuger]

Hi again @Anonymous 

Thanks for the further detail!

I have given this some thought and tested a few examples

I believe that if you want true flexibility to change contribution & compounding frequencies (beyond 12 & 1), you need to write code to generate a table of contributions and calculate the compounded values, without using the built-in FV function.

I have come up with a measure that (hopefully) does this, given the below assumptions (including some of the standard assumptions of FV):

• Future value is to be determined at time T (expressed in years).
• Value at time 0 = P.
• Annual compounding rate = r.
• Contributions of PMT are made n times per year, at these points in time (expressed as fractions of a year): 1/n, 2/n, 3/n, ..., (Tn-1)/n, T.
  (end of period contributions)
• Compounding occurs m times per year, at these points ub time (expressed as fractions of a year): 1/m, 2/m, 3/m, ..., (Tm-1)/m, T.
• Compounding rate at each instance of compounding = r/n.
• When compounding occurs, cumulative contributions made before that point in time are compounded. Any contributions made at exactly the same time are not compounded at that point in time (this is consistent with the FV function when n=m).

Here is the actual measure based on your original one. I have also used SWITCH rather than nested IF, and removed table qualifiers from measure references. This should work for any arbitrary values of n & m:

Expected Return w/ Regular Contributions =
VAR n =
    // Frequency of compound
    SWITCH (
        SELECTEDVALUE ( 'Compound Frequency'[Compound Frequency] ),
        "Annually", 1,
        "Monthly", 12
    )
VAR m =
    // Frequency of contribution annually
    SWITCH (
        SELECTEDVALUE ( 'Contribution Frequency'[Contrib Freq] ),
        "Annually", 1,
        "Monthly", 12
    )
VAR r = [Annual Rate of Return Value]
VAR P = [PV Value]
VAR t =
    ( MAX ( DatesTBL[Year] ) - YEAR ( TODAY () ) ) // Expressed in years
VAR PMT_ = [PMT Value] // Unadjusted value of each Contribution
VAR r_adj = r / n
-- Create list of index values for each Contribution, to be iterated over by SUMX
-- When @ContributionIndex=0,   time = 0
-- When @ContributionIndex=T*m, time = T
VAR Index =
    SELECTCOLUMNS ( GENERATESERIES ( 0, T * m ), "@ContributionIndex", [Value] )
VAR Result =
    SUMX (
        Index,
        VAR i = [@ContributionIndex]
        VAR Contribution =
            IF ( [@ContributionIndex] = 0, P, PMT_ )
        // RemainingCompoundingCount = # of future compounding occurrences
        // as at the current Contribution
        VAR RemainingCompoundCount =
            CEILING ( n * ( T * m - i ) / m, 1 )
        VAR CompoundingMultiplier = ( 1 + r_adj ) ^ RemainingCompoundCount
        VAR ContributionFV = Contribution * CompoundingMultiplier
        RETURN
            ContributionFV
    )
RETURN
    Result

There may well be some things you want to adjust here (maybe compounding logic) but hopefully this is a useful starting point.

Regards,

Owen

[Anonymous]

Hi @OwenAuger,

Thank you for the detailed response. It will take a little bit of time for me to process and understand what you have done there. I haven't seen variable created within an expression before. 

However, I can see that it does output figures close to what I am expecitng and I'm sure this will be a strong foundation for me to build on.

Out of curiousity, for an interest rate that is compounded yearly, how do interest rates get applied to montly contributions in this model? To be more specific, consider the following example:

• if I open a bank account on the 01/01/2022
• PV = $1000
• PMT = $100/monthly
• i = 5% compounded yearly
• deposits made at end of period

At t = 1yr, the amount in the bank would be 1000 + 1200 =2200. However, the deposits would not have been in the account for a year (eg January's deposit would have been in for 11months, February 10 months and so forth). Is it correct to apply interest rate based on the total amount collected up until the next compounding year OR does interest only get applied to the amount that has been in for 1 year (i.e. the 100 would experience compound at the end of Jan 2023).

I am assuming that the first case is what happens, in which case, if the compounding frequency was yearly, it wouldnt matter if you contributed $100/month or if you added $1200 on the 31st Dec 2021. 

Sorry for the long question, but I believe this will have an impact on the way I set up the measure(s)