Variable compound interest calculation help

[Oyseka]

Hi All,

I am struggling to create a calculation that lets me forecast income over variable time scales with variable interest rates.

There are a number of contracts with terms ranging from 24 to 60 months which have built in escalation clauses based on the PPI. I am trying to build a report that lets me view the expected income for the remaining contract period but the calculation seems way too clutzy and this only works on a 60 month contract so I have to render further case statements for 48 months, 36 months etc;

Fields;

 

Months (the duration of the contract)

Elapsed Time (The number of months already past in the contract)

Remaining Time (The number of months left)

Rental (Initial rental cost)

Year 2 Rental (Initial rental * PPI plus initial rental)

Year 3 Rental (Year 2 Rental * PPI plus year 2 rental)

Year 4 Rental (Year 3 Rental * PPI plus year 3 rental)

Year 5 Rental (Year 4 Rental * PPI plus year 4 rental)

 

Additionally at the anniversary of each contract start date I need to change the calculation field Year 2 Rental, Year 3 Rental etc to a fixed value instead of the calculation.

 

This is the forecast value calculation:

 

Case (Remaining_Time>48 and Remaining_Time<60  and Escalation="Yes" ;(12*Year_Five_Rental)+(12*Year_Four_Rental)+(12*Year_Three_Rental)+(12*Year_Two_Rental)+((Remaining_Time-48)*Rental);Remaining_Time>36 and Remaining_Time<48  and Escalation="Yes" ;(12*Year_Five_Rental)+(12*Year_Four_Rental)+(12*Year_Three_Rental)+((Remaining_Time-36)*Year_Two_Rental) ;Remaining_Time>24 and Remaining_Time<36  and Escalation="Yes" ;(12*Year_Three_Rental)+(12*Year_Two_Rental)+((Remaining_Time-24)*Rental);Remaining_Time<24  and Remaining_Time>12  and Escalation ="Yes" ;(12*Year_Five_Rental)+((24-Time_Elapsed)*Year_Four_Rental);Remaining_Time<12 and Escalation="Yes";Remaining_Time*Year_Five_Rental;Remaining_Time*Rental)

 

Any help appreciated

[eos]

Try this one. It works, though I'm not sure for what exactly, since your example calculation wasn't internally consistent, at least not to me (which is no surprise, given it formatting, or lack thereof).

 

If nothing else, this gives you an idea of how to simplify a complex calculation.

 

Where indicated in the comment, replace the hardcoded sample values with your field references. This looks nicer with indentation. 

Case (
True ; // replace with Escalation="Yes" ;
Let (
[
monthsCount = 24 // Months field
; slot = Int ( ( monthsCount + 11 ) / 12 ) // one of the five 'slots' 12, 24, 36, 48, 60
; elapsed = 5 // Elapsed field, instead of Remaining Time; see next assignment
; theRemain = ( ( slot ) * 12 ) - Elapsed // check if this is the desired calculation
; y5 = 150 // field Year_Five_Rental
; y4 = 140 // field Year_Four_Rental
; y3 = 130 // field Year_Three_Rental
; y2= 120 // field Year_Two_Rental
; y1 = 110 // field Rental
; myList = List ( y2 ; y3 ; y4 ; y5 )
; myList = LeftValues ( myList ; slot - 1 )
; myList = Left ( myList ; Length ( myList ) - 1 ) // get rid of the trailing ¶
; myList = Substitute ( myList ; ¶ ; "+" ) // 'turn inanimate LISTstuff into living code' (to paraphrase DRH)
; theResult = ( Evaluate ( myList ) * 12 ) + ( theRemain * y1 ) // evaluate the expression and add the remainder
] ;
theResult
)
)

[comment]

If I understand correctly (and that's a big IF), you have the following data:

Payment (the amount of monthly payment in the first year);
GrowthRate (the rate by which Payment increases each year);
Months (the duration of the contract in months);
StartDate

Everything else can be calculated from the data above - I don't see the need for any more fields (unless these are calculation fields to display interim results).


Now, the total amount paid after N months have elapsed can be calculated as =
 

Let ( [
y = Div ( N ; 12 ) ;
m = Mod ( N ; 12 ) ;
r = 1 + GrowthRate ;
sumYpayments = 12 * Payment * ( 1 - r^y ) / ( 1 - r ) ;
nextMpayment = Payment * r^y
] ;
sumYpayments + m * nextMpayment
)

To calculate the total amount to be received during the lifetime of the contact, make N = Months. To calculate the total amount received  so far, calculate the number of months already elapsed and use that as N. The difference between the two is the total amount to be yet received.

Note that this is the nominal amount - not future value, which is something else altogether.

[eos]

No, it's a way to sum n values of the list ( y2 ; y3 ; y4 ; y5 ), according to the length of the contract.

[Oyseka]

Good Morning eos and comment, sorry to be late replying but we have had no power for two days so I have just received these replies and am looking at them now

[Oyseka]

If I understand correctly (and that's a big IF), you have the following data:

Payment (the amount of monthly payment in the first year);

GrowthRate (the rate by which Payment increases each year);

Months (the duration of the contract in months);

StartDate

Everything else can be calculated from the data above - I don't see the need for any more fields (unless these are calculation fields to display interim results).

Now, the total amount paid after N months have elapsed can be calculated as =

 

Let ( [
y = Div ( N ; 12 ) ;
m = Mod ( N ; 12 ) ;
r = 1 + GrowthRate ;
sumYpayments = 12 * Payment * ( 1 - r^y ) / ( 1 - r ) ;
nextMpayment = Payment * r^y
] ;
sumYpayments + m * nextMpayment
)

To calculate the total amount to be received during the lifetime of the contact, make N = Months. To calculate the total amount received  so far, calculate the number of months already elapsed and use that as N. The difference between the two is the total amount to be yet received.

Note that this is the nominal amount - not future value, which is something else altogether.

Good Morning comment, you understand correctly the information that I have with the exception that the growth rate changes from year to year such that it may be 7% at the start of year 2 and perhaps 5% at the start of year three etc. I therefore need to fix the payments for the preceding years and forecast on the new value for the remaining period. With that exception your solution does indeed provide what I am looking for,

[Oyseka]

Try this one. It works, though I'm not sure for what exactly, since your example calculation wasn't internally consistent, at least not to me (which is no surprise, given it formatting, or lack thereof).

If nothing else, this gives you an idea of how to simplify a complex calculation.

Where indicated in the comment, replace the hardcoded sample values with your field references. This looks nicer with indentation.

Case (True ; // replace with Escalation="Yes" ;Let ([monthsCount = 24 // Months field; slot = Int ( ( monthsCount + 11 ) / 12 ) // one of the five 'slots' 12, 24, 36, 48, 60; elapsed = 5 // Elapsed field, instead of Remaining Time; see next assignment; theRemain = ( ( slot ) * 12 ) - Elapsed // check if this is the desired calculation; y5 = 150 // field Year_Five_Rental; y4 = 140 // field Year_Four_Rental; y3 = 130 // field Year_Three_Rental; y2= 120 // field Year_Two_Rental; y1 = 110 // field Rental; myList = List ( y2 ; y3 ; y4 ; y5 ); myList = LeftValues ( myList ; slot - 1 ); myList = Left ( myList ; Length ( myList ) - 1 ) // get rid of the trailing ¶; myList = Substitute ( myList ; ¶ ; "+" ) // 'turn inanimate LISTstuff into living code' (to paraphrase DRH); theResult = ( Evaluate ( myList ) * 12 ) + ( theRemain * y1 ) // evaluate the expression and add the remainder] ;theResult))

Good Morning eos, thank you for your response. I apologise for the lack of formatting in my post, it was remiss of me and does not make it any easier for people to assist. Your solution does indeed work as well, thank you for your valuable assistance.

[comment]

the growth rate changes from year to year such that it may be 7% at the start of year 2 and perhaps 5% at the start of year three etc. I therefore need to fix the payments for the preceding years

 

I don't think that's a good idea for several reasons, most of all because you should not be replacing data with the result of a calculation. If you have multiple growth rates, then you should also have a permanent record of each year's growth rate value.

Strictly speaking, that would mean creating a related record for each year. However, I believe repeating fields would be an adequate alternative in this case - see the attached file for a quick sketch.

VarGrowthRateR.fp7.zip

[Oyseka]

Wow, thank you comment. Elegant and easy