A Guide to the PMT, FV, IPMT and PPMT Functions

A Guide to the PMT, FV, IPMT and PPMT Functions

In MS Excel we have the PMT, FV, IPMT and PPMT functions, which do a fantastic job for interest rate calculations.  But what if you don't have Excel ?

This article is for programmers looking to reverse engineer the PMT, FV, IPMT and PPMT functions to incorporate them into other application(s) or programming language(s) as a guide to the behaviors and implementation logic of the afore-mentioned methods.  Non-programmers looking for a better understanding of the listed mortgage formulas found in popular office programs like MS Access, MS Excel and OpenOffice.org Calc (Spreadsheet), this article is for you too as I will keep the main content non-technical with clear segmentation on programming-specific concepts so that you can skip around easily if you are not interested in code.


Contents
Bonus Sections

Algorithm:
PMT() Function

The fixed monthly payment for a fixed rate mortgage is the amount paid by the borrower every month that ensures that the loan is paid off in full with interest at the end of its term. This monthly payment c depends upon the monthly interest rate r (expressed as a fraction, not a percentage, i.e., divide the quoted yearly percentage rate by 100 and by 12 to obtain the monthly interest rate), the number of monthly payments N called the loan's term, and the amount borrowed P known as the loan's principal; c is given by the formula:

   c = (r / (1 - (1 + r)^-N))P    

-- source: en.wikipedia.org/wiki/Mortgage_calculator

Nota bene: r is a decimal representation of the per payment interest rate as suggested in the quoted text, but is not always monthly as stated.  This value should be expressed in whatever frequency the interest is compounded in like weekly or semi-annually as may be the case on some loans or annuities, especially in certain countries.  However, for the remainder of the discussion, we will be looking at a mortgage loan paid monthly; therefore, we will continue with 12 as the applicable divisor.

With all that said, the above formula is the spreadsheet PMT() function at its basic level; however, we know it is not complete as Excel, for example, allows for two other factors to the calculated payment amount: fv and type.  But it is a good starting point representing the formula when fv and type are both 0.

The fv or future value establishes the amount of the loan or annuity that will be left after making all the payments specified by N or Nper as designated in Excel.

The type specifies if the loan is paid at beginning of period or the end.

Aside from these additional options, take a look at a sample scenario in spreadsheet:
Cell {B9} could have this simple formula :

   =PMT(B1/B2,B4*B2,B5)    
   =PMT(0.07/12,30*12,165000)    

...but has this one :

   =PMT(B1/B2,B4*B2,B5,B6,B7)    
   =PMT(0.07/12,30*12,165000,0,0)    

Both yield the exact same results, since fv and type both default to 0 anyway.  Speaking of the results, you will notice they are negative.  A good explanation I have seen previously is to think of this as "cash flow".  The loan amount is positive, since this is cash flowing "in" to you from the lender; similarly, the payment response is negative since this would be cash flowing "out".  If you use a negative pv, then you would be looking at investing or spending money in order to receive a positive monthly return.  

Checkpoint:
PMT-FV-IPMT-PPMT.xls
«« examine columns {B} and {F} changing values for fv and type to get comfortable with the positive / negative concept, using cells {E6} and {E7} as a hint.


With our new knowledge, here is an updated formula from above multiplying the principal by -1 to account for change in cash flow:

   c = (r / (1 - (1 + r)^-N)) * -P    

Now that we are comfortable with why we get negative returns, let's circle back to the two optional parameters seen in Excel/Calc and how they affect the formula.

Future Value
Since making N payments results in some future value, it is very reasonably deduced that the principal by which the payment amount should be determined is the principal at period n subtracted from the principal at the start of the loan.  However, try =PMT(0.07/12, 360, 164000) and =PMT(0.07/12, 360, 165000, -1000); do you get the same answer?

Why not?

Well, if I start with x amount of dollars and interest is compounded monthly from now until 30 years from now, $1,000.00 probably didn't start as such; therefore, the reason the answers are not the same is that we have to subtract the number that when interest for that period is applied equals $1,000.00 and not $1,000.00 itself.  

Since, the following is calculation for future value using simple compounded interest like with a savings account with no fees or withdrawals:

   fv = p0 * (1 + r)^N    

The present value that would yield a specific future value would be:

   p0 = fv / (1 + r)^N    

For reasons to be discussed later or simply to avoid negative exponents as we have with our original formula, we will multiply our original formula by (1 + r)^N, so that we end up with:

   c = r / ((1 + r)^N - 1) * -(pv * (1 + r)^N + fv)    

The original (1 - (1 + r)^-N) multiplied by (1 + r)^N reduces nicely to ((1 + r)^N - 1) and we can extract the "-" from both pv and fv to end up with -(pv * (1 + r)^N + fv).  In plain English, this reads the interest rate divided by the aggregate interest rate times the negative of the compounded principal minus the balance at the end of payments equals the payment amount per period.  

Checkpoint:
«« do you recognize what this is doing: pv * (1 + r)^N?
«« why is the fv passed above -1000 instead of 1000?

double pmt(double r, int nper, double pv, double fv, int type) {
                          double pmt = r / (Math.pow(1 + r, nper) - 1)
                                  * -(pv * Math.pow(1 + r, nper) + fv);
                          
                          return pmt;
                      }

Select all Open in new window

Loan Type
As previously mentioned, the loan type in Excel with respect to PMT() and the other functions discussed indicates if payment is being made at beginning of payment period or the end.  The significance is that payment at the end would mean that interest has already been applied by the time of the payment and so more of the payment goes to interest and thus less to the principal which affects to subsequent interest calculation.  Conversely, with payments at beginning of a period, interest would not be applied as yet so from the initial payment you are already dealing with a smaller principal on which future interest calculations are done.  The net result is the payment for same loan amount is reduced.

Take a look at cells {D9} and {E9} as compared to cell {B9} to see an illustration of the change.

So continuing on the thought of the principal, its future value would reduce by one interest compounding or essentially compounded over one less payment (i.e., N-1):

   fv = p0 * (1 + r)^(N-1)    

Mathematically equivalent is this formula:

   fv = p0 * (1 + r)^N / (1 + r)    

Therefore, an easy way to update our above formula conditionally would be: if type equals "1", then divide by (1 + r); if type equals "0", then divide by 1 (or leave "as-is").

if (type == 1) {
                         pmt = pmt / (1 + r);
                      }

Select all Open in new window

if (type == 1) pmt /= (1 + r);

Select all Open in new window

Algorithm:
FV() Function

Pushing on, let's take a look at the FV() function which has this signature in Excel:

   FV(interest_rate, number_payments, payment, PV, Type)    

From the above, we can place a few of the parameters immediately into our simple future value formula:

   fv = pv * (1 + r)^N    

What are we missing?

Well, let's start going through the parameters.  Interest rate is represented by r; number of payments, N; payment, ?.  Payment is not yet represented, but is very key here as we are paying monthly; therefore, the interest will be applied to a new balance each time.  Consequently, we will need to adjust for periodic payments in our fv calculation.  To start, we will take our payment algorithm above and back into this truth:

   c * ((1 + r)^N - 1) / r + pv * (1 + r)^N + fv = 0    

If you think about the fact that our pv was $165,000.00 (positive) and our c was -$1,097.75 (negative), then it should become obvious that an accumulation of all payments plus the compounded principal and interest should equal zero.

With that said, we will re-look at our spreadsheet:
Cell {B10} has this formula :

   =FV(B1/B2,B3,B9,B5,B7)    
   =FV(0.07/12,24,-1097.75,165000,0)    

Note that nper / N / number_payments is referred to the same as it was in PMT(); however, represents the period number which we want to find future value for (i.e., payment 24 versus 360 total payments).  This makes sense when you really think about it as otherwise the result would always be 0 or whatever you started with as fv when solving for c.

Agree ?

Okay, since we want to solve for fv, let's solve for it in the equation above.  Since this is addition and we have fv all by itself (conveniently), let's just subtract the other two operands from both sides of the equation:

   fv = -(c * ((1 + r)^N - 1) / r + pv * (1 + r)^N)    

Great ! That is it really...

Checkpoint:
«« play with some different period numbers (cell {B3}) and see shifts in data (cell {B10}).

double fv(double r, int nper, double c, double pv, int type) {
                          double fv = -(c * (Math.pow(1 + r, nper) - 1) / r + pv
                                  * Math.pow(1 + r, nper));
                          
                          return fv;
                      }

Select all Open in new window

Loan Type
One last touch is simply to account for the (loan) type as we did in PMT().

refresher on type impact on c formula.
   c = r / ((1 + r)^N - 1) * -(pv * (1 + r)^N + fv) / (1 + r)    

without going into the long-hand, since pv and fv are both affected by the division by (1 + r) to solve for fv we have to multiply both sides of the equation by (1 + r).

   fv = -(c * (1 + r) * ((1 + r)^N - 1) / r + pv * (1 + r)^N)    

if (type == 1) {
                         c = c * (1 + r);
                      }

Select all Open in new window

Now that we are into reading the future, let's look at the final two ...

Algorithm:
IPMT() Function

IPMT() or the interest portion of a future payment specified by the period argument introduced in the Excel version:

   IPMT(interest_rate, period, number_payments, PV, FV, Type)    

Period
Period is the only different value and has already been discussed above in our FV() section and is stored in cell {B3}.  All the other values match up with PMT() signature:

   PMT(interest_rate, number_payments, PV, FV, Type)    

So how do we use this ?

My take on IPMT() is it is the simple interest of the future balance of the period before the one requested, so using our future value algorithm:

   ipmt = -(c * ((1 + r)^(per-1) - 1) / r + pv * (1 + r)^(per-1)) * r    
or
   ipmt = FV(r, per-1, c, pv, type) * r    

Where c = PMT(r, N, pv, fv, type), so fully utilizing not only what we have learned but what is already built in FV() and PMT() we can satisfy IPMT() as:

   ipmt = FV(r, per-1, PMT(rate, nper, pv, fv, type), pv, type) * r    

Loan Type
And adjusting for type is simply dividing by (1 + r) for same reasoning we did with PMT().

Checkpoint:
«« try the formula =FV(B1/B2,B3-1,B9,B5,B7)*B1/B2 or =FV(0.07/12,24-1,-1097.75,165000,0)*0.07/12 in cell {B14} and see what you get.

double ipmt(double r, int per, int nper, double pv, double fv, int type) {
                          
                          double ipmt = fv(r, per - 1, pmt(r, nper, pv, fv, type), pv, type) * r;
                          
                          if (type == 1) ipmt /= (1 + r);
                          
                          return ipmt;
                      }

Select all Open in new window

Algorithm:
PPMT() Function

Glad you are still here as hopefully by now you are seeing this gets so much easier building on what we learned in PMT() segment.  Continuing this trend, the PPMT() function is simply the complement of the IPMT() calculation with respect to the payment amount c.  In other words ...

   ppmt = c - (-(c * ((1 + r)^(per-1) - 1) / r + pv * (1 + r)^(per-1)) * r)    
   ppmt = c + (c * ((1 + r)^(per-1) - 1) / r + pv * (1 + r)^(per-1)) * r    
or
   ppmt = c - FV(r, per-1, c, pv, type) * r    

And taking same philosophy or re-usability, this is even simpler with usage of the PMT() and IPMT() functions:

   ppmt = PMT(r, N, pv, fv, type) - IPMT(r, per, N, pv, fv, type)    

Voila !

You will see that using formula in ...

Cell {B12}

   =PPMT(B1/B2,B3,B4*B2,B5,B6,B7)    
   =PPMT(0.07/12,24,30*12,165000,0,0)    

or Cell {B13}

   =B9-B11    
   =-1097.75-(-943.14)    

... results in the same value.

double ppmt(double r, int per, int nper, double pv, double fv, int type) {
                          return pmt(r, nper, pv, fv, type) - ipmt(r, per, nper, pv, fv, type);
                      }

Select all Open in new window

Development:
Additional Programming Concepts

Aside from data types, as developers we have several other considerations.

Utility Class
Utility classes are very handy and are great for grouping related functions like PMT, FV, IPMT and PPMT, while still promoting re-use by not having directly in your mortgage application class for example.

public class Financials {
                          public static double pmt(double r, int nper, double pv, double fv, int type) {
                              // ...
                          }
                      }

Select all Open in new window

Note the modifiers public and static .  Basically, utilities are typically good candidates for being run without needing a specific instance of a class since they don't depend on state of the class as their signatures account for all the values needed to do processing that leads to the desired output.

Other languages have similar constructs, so a good way to start things off.  For me, in Java, I chose Financials and static functions so that I can use in code as Financials.pmt(...) like Math.pow(...) was used in our code above.

Optional Input Values
In Calc, Excel and other programs with existing PMT, FV, IPMT and PPMT functions, you can typically (as shown above) call PMT(r,N,pv) or PMT(r,N,pv,fv) if you want to default the other arguments.  Each language handles this a bit differently, like Visual Basic allows the Optional keyword which could be used for fv or type so that you could use one method that is called three different ways.

Another way to accomplish this in programming is to use method overloading.  Method overloading is simply multiple versions of the same function or subroutine which have very similar signatures (importantly the name which is what separates overloading from simply having common interfaces) but which differ in number and possibly data type of the inputs.

For example, here is an overload of pmt().

public static double pmt(double r, int nper, double pv, double fv) {
                          return pmt(r, nper, pv, fv, 0);
                      }

Select all Open in new window

Checkpoint:
«« try coding method overloads for the ipmt() and ppmt() procedures.


Parsing Input / Formatting Output
On the topic of input values, consider again our choice on which data types to use.  Along those lines, I have directly "mimicked" the requirements of PMT(), et al from Excel; however, you may choose to allow users to input strings percentages like "7.00%" for example.  If so, you will need to parse this value appropriately before using in formulas which expect a decimal rate.

Consequently, whether you do or do not choose this for input, you may definitely need format the output, especially using double data type versus one with precise number of decimal places since most users are use to seeing the results as "($1,097.75)" or "-$1,097.75" instead of -1097.7491170456508.  The huge caveat is knowing your system and usage of the output.  If you are always printing the output, it may not matter if your functions return strings or numbers; however, I tend to keep numbers as numbers to make math easy (without parsing) and then format for display, so I tend to use the same utility class and public static (shared) method syntax to make life easy instead of re-coding the formatting in each calling class.  This is just best practice programming, but just good to say out load as a reminder.

Checkpoint:
«« play with the full Java source in Appendix A to see sample implementations of parsing / formatting functions that can be created.

Development:
Database-Specific Concepts

Utility Database
Like mentioned for non-database programming, it is nice to have a utility database dedicated to special functions.  
You may even consider making these into system stored procedures in MS SQL Serer or the like.
(info: sqlservernation.com/blogs/howtos/archive/2009/10/29/create-a-system-stored-procedure.aspx).  

You can try it out yourself and play with using different schema, so you can call like financials.pmt(...) as shown with Java.

Know Your DB's Programming Capabilities
Most of the systems I am familiar with have standard math functions like pow() or power() for handling exponents; however, you should ensure your system does.  In addition to this, you can learn which shortcuts provided by your system can come in handy.  For example, in Java, we could have done the division by (1 + r) directly in our primary formula by using an inline if statement like (type==1?1 + r:1).  In database systems you can do this...

MySQL:

if(type=1, 1 + r, 1)

Select all Open in new window

MySQL, MS SQL, et al:

case type when 1 then 1 + r else 1 end

Select all Open in new window

Have a database other than MySQL or MS SQL, give the above a try, especially the latter as most systems will support some variation of one of the above if not both.

Data Types Revisited
Data types again should be approached with care as (more so than non-database programming languages) SQL may react differently to mathematical operators based on the data types involved in calculation.  For some, the logic translates almost identically (see Appendix B's sample PMT() function in MySQL); however, MS SQL Server for example will do integer division when dealing with two integer values.  In this scenario, there should always be a decimal involved, but just an example that comes to mind.  Explore for yourself to ensure there are no other gotchas in your system.

Checkpoint:
«« using code sample in Appendix B, try creating all four functions and handle default values as discussed.
«« MS SQL Server users, give this a try with DECIMAL(p[,s]) data type and see if you get same results.
«««« Why or why not? (see Appendix C)


One note going back to the data types point is for systems without a double data type, you will most likely get different responses than Excel due to precision of percentage or payment values passed in that may be very long floating point values since they are based on calculations themselves.

Hint: since MS SQL Server is one of the systems without a double data type, you may want to explore how T-SQL implements some other numeric data types that can change storage size.  
Is there a float data type, maybe ?! (info: msdn.microsoft.com/en-us/library/ms173773.aspx)

Summary

Well that is it in a nutshell.  Hopefully you have enjoyed and found this quide useful (and thus voted YES below).  Consequently, for those embarking of developing your own, the best of luck to you now that you have a deeper understanding of the logic behind the PMT, FV, IPMT and PPMT functions.  For all, a completed Financials utility class written in Java is included in Appendix A for you to play with and/or reuse.

Acknowledgements
Special thanks to Friend and EE Member mark_wills for his contributions in my writing of this Article!  

Thanks to EE Member harfang for writing the EE article Articles and Typography: a brief manual which came in handy on a few of this article's formatting requirements.

Tremendous thanks to the EE Page Editors like aikimark who have made this and other articles better, so that hopefully you have enjoyed reading along to make it this far for me to say thank you to all of you for taking the time to read this.

Just remember, if you liked this Article, it helped you solve an issue reproducing these functions, enlightened you a bit or just because...please remember to vote Yes below to show your support.

Thank you again for reading...

Happy coding!

Best regards,

Kevin (aka MWVisa1)

Appendix A: Java

Full source: Financials utility class.

package crossedlogic.utils;
                          
                      import java.text.NumberFormat;
                      import java.text.ParseException;
                          
                      public class Financials {
                          private static final NumberFormat nfPercent;
                          private static final NumberFormat nfCurrency;
                          
                          static {
                          
                              // establish percentage formatter.
                              nfPercent = NumberFormat.getPercentInstance();
                              nfPercent.setMinimumFractionDigits(2);
                              nfPercent.setMaximumFractionDigits(4);
                          
                              // establish currency formatter.
                              nfCurrency = NumberFormat.getCurrencyInstance();
                              nfCurrency.setMinimumFractionDigits(2);
                              nfCurrency.setMaximumFractionDigits(2);
                          }
                          
                          /**
                           * Format passed number value to appropriate monetary string for display.
                           * 
                           * @param number
                           * @return localized currency string (e.g., "$1,092.20").
                           */
                          public static String formatCurrency(double number) {
                              return nfCurrency.format(number);
                          }
                          
                          /**
                           * Format passed number value to percent string for display.
                           * 
                           * @param number
                           * @return percentage string (e.g., "7.00%").
                           */
                          public static String formatPercent(double number) {
                              return nfPercent.format(number);
                          }
                          
                          /**
                           * Convert passed string to numerical percent for use in calculations.
                           * 
                           * @param s
                           * @return <code>double</code> representing percentage as a decimal.
                           * @throws ParseException
                           *             if string is not a valid representation of a percent.
                           */
                          public static double stringToPercent(String s) throws ParseException {
                              return nfPercent.parse(s).doubleValue();
                          }
                          
                          /**
                           * Emulates Excel/Calc's PMT(interest_rate, number_payments, PV, FV, Type)
                           * function, which calculates the mortgage or annuity payment / yield per
                           * period.
                           * 
                           * @param r
                           *            - periodic interest rate represented as a decimal.
                           * @param nper
                           *            - number of total payments / periods.
                           * @param pv
                           *            - present value -- borrowed or invested principal.
                           * @param fv
                           *            - future value of loan or annuity.
                           * @param type
                           *            - when payment is made: beginning of period is 1; end, 0.
                           * @return <code>double</code> representing periodic payment amount.
                           */
                          public static double pmt(double r, int nper, double pv, double fv, int type) {
                          
                              // pmt = r / ((1 + r)^N - 1) * -(pv * (1 + r)^N + fv)
                              double pmt = r / (Math.pow(1 + r, nper) - 1)
                                      * -(pv * Math.pow(1 + r, nper) + fv);
                          
                              // account for payments at beginning of period versus end.
                              if (type == 1)
                                  pmt /= (1 + r);
                          
                              // return results to caller.
                              return pmt;
                          }
                          
                          /**
                           * Overloaded pmt() call omitting type, which defaults to 0.
                           * 
                           * @see #pmt(double, int, double, double, int)
                           */
                          public static double pmt(double r, int nper, double pv, double fv) {
                              return pmt(r, nper, pv, fv, 0);
                          }
                          
                          /**
                           * Overloaded pmt() call omitting fv and type, which both default to 0.
                           * 
                           * @see #pmt(double, int, double, double, int)
                           */
                          public static double pmt(double r, int nper, double pv) {
                              return pmt(r, nper, pv, 0);
                          }
                          
                          /**
                           * Emulates Excel/Calc's FV(interest_rate, number_payments, payment, PV,
                           * Type) function, which calculates future value or principal at period N.
                           * 
                           * @param r
                           *            - periodic interest rate represented as a decimal.
                           * @param nper
                           *            - number of total payments / periods.
                           * @param c
                           *            - periodic payment amount.
                           * @param pv
                           *            - present value -- borrowed or invested principal.
                           * @param type
                           *            - when payment is made: beginning of period is 1; end, 0.
                           * @return <code>double</code> representing future principal value.
                           */
                          public static double fv(double r, int nper, double c, double pv, int type) {
                          
                              // account for payments at beginning of period versus end.
                              // since we are going in reverse, we multiply by 1 plus interest rate.
                              if (type == 1)
                                  c *= (1 + r);
                          
                              // fv = -(((1 + r)^N - 1) / r * c + pv * (1 + r)^N);
                              double fv = -((Math.pow(1 + r, nper) - 1) / r * c + pv
                                      * Math.pow(1 + r, nper));
                          
                              // return results to caller.
                              return fv;
                          }
                          
                          /**
                           * Overloaded fv() call omitting type, which defaults to 0.
                           * 
                           * @see #fv(double, int, double, double, int)
                           */
                          public static double fv(double r, int nper, double c, double pv) {
                              return fv(r, nper, c, pv);
                          }
                          
                          /**
                           * Emulates Excel/Calc's IPMT(interest_rate, period, number_payments, PV,
                           * FV, Type) function, which calculates the portion of the payment at a
                           * given period that is the interest on previous balance.
                           * 
                           * @param r
                           *            - periodic interest rate represented as a decimal.
                           * @param per
                           *            - period (payment number) to check value at.
                           * @param nper
                           *            - number of total payments / periods.
                           * @param pv
                           *            - present value -- borrowed or invested principal.
                           * @param fv
                           *            - future value of loan or annuity.
                           * @param type
                           *            - when payment is made: beginning of period is 1; end, 0.
                           * @return <code>double</code> representing interest portion of payment.
                           * 
                           * @see #pmt(double, int, double, double, int)
                           * @see #fv(double, int, double, double, int)
                           */
                          public static double ipmt(double r, int per, int nper, double pv,
                                  double fv, int type) {
                          
                              // Prior period (i.e., per-1) balance times periodic interest rate.
                              // i.e., ipmt = fv(r, per-1, c, pv, type) * r
                              // where c = pmt(r, nper, pv, fv, type)
                              double ipmt = fv(r, per - 1, pmt(r, nper, pv, fv, type), pv, type) * r;
                          
                              // account for payments at beginning of period versus end.
                              if (type == 1)
                                  ipmt /= (1 + r);
                          
                              // return results to caller.
                              return ipmt;
                          }
                          
                          /**
                           * Emulates Excel/Calc's PPMT(interest_rate, period, number_payments, PV,
                           * FV, Type) function, which calculates the portion of the payment at a
                           * given period that will apply to principal.
                           * 
                           * @param r
                           *            - periodic interest rate represented as a decimal.
                           * @param per
                           *            - period (payment number) to check value at.
                           * @param nper
                           *            - number of total payments / periods.
                           * @param pv
                           *            - present value -- borrowed or invested principal.
                           * @param fv
                           *            - future value of loan or annuity.
                           * @param type
                           *            - when payment is made: beginning of period is 1; end, 0.
                           * @return <code>double</code> representing principal portion of payment.
                           * 
                           * @see #pmt(double, int, double, double, int)
                           * @see #ipmt(double, int, int, double, double, int)
                           */
                          public static double ppmt(double r, int per, int nper, double pv,
                                  double fv, int type) {
                          
                              // Calculated payment per period minus interest portion of that period.
                              // i.e., ppmt = c - i
                              // where c = pmt(r, nper, pv, fv, type)
                              // and i = ipmt(r, per, nper, pv, fv, type)
                              return pmt(r, nper, pv, fv, type) - ipmt(r, per, nper, pv, fv, type);
                          }
                      }

Select all Open in new window

Appendix B: MySQL

Full source: example PMT() procedure.
(info: dev.mysql.com/doc/refman/5.0/en/create-procedure.html)

delimiter $$ 
                      
                      -- optionally create a utility schema for Financial functions.
                      create schema if not exists `Financials`$$
                      
                      -- drop existing PMT() function.
                      drop function if exists `Financials`.`PMT`$$
                          
                      -- create new PMT() function with signature:
                      -- Pmt( interest_rate, number_payments, PV, FV, Type )
                      create function `Financials`.`PMT`(
                         r double,
                         nper int,   
                         pv double,
                         fv double,
                         type int
                      ) returns double
                      begin
                         declare pmt double; 
                          
                         select r / (pow(1 + r, nper) - 1) 
                            * -(pv * pow(1 + r, nper) + fv) 
                            / if(type=1, 1 + r, 1) 
                         into pmt;
                          
                         return pmt; 
                      end$$ 
                          
                      delimiter ;

Select all Open in new window

Appendix C: MS SQL

Full source: example PMT() procedure.
(info: msdn.microsoft.com/en-us/library/ms186755.aspx)

-- optionally create a utility schema for Financial functions.
                      create schema financials;
                      go
                      
                      -- drop existing PMT() function.
                      if exists(
                         select 1
                         from sys.objects 
                         where object_id = OBJECT_ID(N'[financials].[pmt]') 
                            and type in (N'FN', N'IF', N'TF', N'FS', N'FT')
                      ) drop function [financials].[pmt];
                      go
                      
                      -- create new PMT() function with signature:
                      -- Pmt( interest_rate, number_payments, PV, FV, Type )
                      create function [financials].[pmt] (
                         @r decimal(38,36),
                         @nper int,
                         @pv decimal(38,19),
                         @fv decimal(38,19),
                         @type tinyint
                      ) returns decimal(38,19)
                      as
                      begin
                         declare @pmt decimal(38,19);
                         
                         set @pmt = @r / (power(1 + @r, @nper) - 1) 
                                  * -(@pv * power(1 + @r, @nper) + @fv) 
                                  / case @type when 1 then 1 + @r else 1.0 end
                         ;-- or --
                         /* 
                         set @pmt = @r / (power(1 + @r, @nper) - 1) 
                                  * -(@pv * power(1 + @r, @nper) + @fv);
                         -- account for payment at beginning of period versus end.
                         if (@type = 1) set @pmt = @pmt / (1 + @r);
                         */
                         
                         return @pmt;
                      end

Select all Open in new window

Nota bene: the variety on data types that can be used.  As previously mentioned, being a small subset of integers, tinyint can be used for the (loan) type.  Since interest rate should be less than 100%, we can hedge our bets a little and go with higher number of places after decimal point - decimal(38,36) would allow for 99.999999... which is pretty much 10,000%, which I would think is more than plenty accounting for odd scenarios.

Glossary

» c or pmt - calculated mortgage or annuity payment / yield per period.
» fv - future value of loan or annuity.
» method signature - procedure name, return type, parameters (typed or not).
» method overload(ing) - procedures whose signatures are same, except one or more parameters differ.
» N or nper - number of total payments / periods.
» P or pv - present value -- borrowed or invested principal.
» period - period (payment number) to check value at in formulas like PPMT or IPMT.
» r or rate - periodic interest rate represented as a decimal.
» type - when payment is made: beginning of period is 1; end, 0.