FV Function - Different compounding frequency and contribution frequency

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