IPMT and PPMT in Essbase

G’day All,

The subtitle for this post could be “How to make sure the guy after you doesn’t hate you – making code legible”

As the newest Ace Associate and java-junky-Jason-Jones points out – code is read significantly more often than it is written

He then also used the term “behooves” correctly – so perhaps he just felt in the mood to wax lyrical.

Anyways.

The underlying logic for the functions PMT\IPMT\PPMT is not that difficult. Basically: for a fixed term loan you are to work out the full repayment, the interest component and the principle component. Obviously as you go through the loan period the ratio of interest to principle decreases, you pay more interest at the front when your principle amount is high and more principle at the end when the amount owing has diminished.

For a thorough and absolutely fantastic walk through of how to convert the excel function into code (including examples of Java & SQL!) you should definitely go and read this article by Kevin Cross . Actually – you should go and read it anyway – it’s an incredibly clear yet detailed discovery document, stepping through all of the issues, giving exceptionally clear examples (and counter-examples) and then finishes with a multitude of questions and answers for specifics. If the rest of world’s software documentation was half as good as this then there would be significantly fewer stressed developers (and a hell of a lot less random posting to stack overflow)!

And in Essbase?
So. To convert what is there to essbase functions. It should be noted that I’m cheating a little – this is really the IPMT and PMT functions in their default state, with a simple loan type and no value remaining at the end.

/* 
Tn is the length of the loan.
n is the period in question.
pv is the present value
rt is the Interest Rate
Lease Payments PMT = (rt / (1 - @POWER((1 + rt),-tn))) * pv;
Interest On Lease IPMT = (pv * rt * (@POWER((rt + 1),(tn + 1)) - @POWER((rt + 1),n))) / ((rt + 1) * (@POWER((rt + 1),tn) - 1));
Principal on Lease (PPMT) = PMT - IMPT */

No worries right? Too easy.

Where it all becomes a bit…messy

I got to actually writing this piece of the code however, and I realised I was going to have a bit of a problem. Tn (length of the loan) was a calculated field based upon a lookup off a smartlist with an override. N was a dynamic calc member. pv was a piece of data entry against a completely different intersection and rt was another lookup, with another potential of an override! Given that the IPMT code was already of a significant length, how could I make this code actually readable?

To give you an example of what this code looked like with all the cross-dimensionals (and note, this doesn’t include the overrides!)

Interest_On_Lease_IPMT = ("Total_Cost"->"Unallocated_Asset_Category"-> "BegBalance"-> &Year_Assump * "Interest_Rate_Override"->"Unallocated_Asset_Category"-> "BegBalance"-> &Year_Assump/12 * (@POWER(("Interest_Rate_Override"->"Unallocated_Asset_Category"-> "BegBalance"-> &Year_Assump/12 + 1),("Asset_Life_Override"->"Unallocated_Asset_Category"-> "BegBalance"-> &Year_Assump * 12 + 1)) - @POWER(("Interest_Rate_Override"->"Unallocated_Asset_Category"-> "BegBalance"-> &Year_Assump/12 + 1),"Loan_Period"))) / (("Interest_Rate_Override"->"Unallocated_Asset_Category"-> "BegBalance"-> &Year_Assump/12 + 1) * (@POWER(("Interest_Rate_Override"->"Unallocated_Asset_Category"-> "BegBalance"-> &Year_Assump/12 + 1),"Asset_Life_Override"->"Unallocated_Asset_Category"-> "BegBalance"-> &Year_Assump * 12) - 1));

Yeah. Not wonderful. And to be honest, once I add in the 3 other alternatives with the overrides, quite ridiculous.

The calculation for IPMT isn’t ever going to change – however, it’s possible (although unlikely) that the data locations might. Therefore, if down the track one of the data locations needs to be updated, somebody will need to go through this function piece by piece ensuring that they only change the right bit.

Clean the yard
So, how to make it easier? How about we make all the data locations variables instead?

var tn = 0;
var n = 0;
var pv = 0;
var rt = 0;

/* Open Calc Block */
Lease_Payments_PMT(
	/* If there is a lease override use it, otherwise use the interest rate */
	IF("Interest_Rate_Override"->"Unallocated_Asset_Category"-> "BegBalance"-> &Year_Assump <> #missing)
		rt = "Interest_Rate_Override"->"Unallocated_Asset_Category"-> "BegBalance"-> &Year_Assump/12;
	ELSE
		rt = "Interest_Rate"->"Unallocated_Asset_Category"-> "BegBalance"-> &Year_Assump/12;
	ENDIF
	/* If there is a asset life override use it, otherwise use the default asset life */
	IF("Asset_Life_Override"->"Unallocated_Asset_Category"-> "BegBalance"-> &Year_Assump <> #missing)
		tn = "Asset_Life_Override"->"Unallocated_Asset_Category"-> "BegBalance"-> &Year_Assump * 12;
	ELSE
		tn = "Asset_Life_Global"-> "Unallocated Organisation"-> "Unallocated Activity"-> "Unallocated Line"-> "BegBalance"-> &Year_Assump * 12;
	ENDIF
	/* Set Present Value and Period */
	pv = "Total_Cost"->"Unallocated_Asset_Category"-> "BegBalance"-> &Year_Assump;
	n = "Loan_Period";
			
	Lease_Payments_PMT = (rt / (1 - @POWER((1 + rt),-tn))) * pv;
	Interest_On_Lease_IPMT = (pv * rt * (@POWER((rt + 1),(tn + 1)) - @POWER((rt + 1),n))) / ((rt + 1) * (@POWER((rt + 1),tn) - 1));
        Principal_on_Lease_PPMT = Lease_Payments_PMT - Interest_On_Lease_IPMT;	
);

Much easier. The code matches up exactly with the actual function (thus making it significantly easier should somebody ever need to additional interest functions) and it’s also much easier to review (and change!) the data locations should they ever need to be updated.

And trust me. The guy after you will thank you for it.

Cheers
Pete