Published on

103,689 Points

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.

I have done a series of testing and validation on my understanding as well as actual programmatic reproduction of the PMT, FV, IPMT and PPMT calculations; however, it is always prudent to fully test within your own environment with your business's common scenarios (use cases), making sure you get what you would expect before using any code shown as examples in a production environment.

The correlation of the PMT, FV, IPMT and PPMT functions is their usage within fixed rate mortgage calculations; therefore, you can research FRM (info: en.wikipedia.org/wiki/Fixe

For all readers, there is a

For the developer types reading, the Java (info: java.sun.com) programming language will be utilized to demonstrate the concepts in code. Programmers using other languages can hopefully follow along using the Java explanations as pseudo code. For database developers, a brief overview of additional concepts for SQL based solutions will be shown with examples from MySQL (info: dev.mysql.com) and MS SQL Server (info: www.microsoft.com/sqlserve

Pure development sections are clearly identified as such and, additionally, code discussions within other sections are highlighted as "Code Example" for you to easily jump to (or over) when reading.

Algorithm: PMT() Function

Algorithm: FV() Function

Algorithm: IPMT() Function

Algorithm: PPMT() Function

Development: Additional Programming Concepts

Development: Database-Specific Concepts

Summary

Appendix A: Java

Appendix B: MySQL

Appendix C: MS SQL

Glossary

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 paymentdepends upon the monthly interest ratec(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 paymentsrcalled the loan's term, and the amount borrowedNknown as the loan's principal; c is given by the formula:P

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

-- source: en.wikipedia.org/wiki/Mortgage_calcu lator

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:

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:

7.00% annual percentage rate.

30 years term (360 total payments - 30 x 12).

$165,000.00 loan amount.

Cell {B9} could have this simple formula :...but has this one :

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.

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:

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.

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?

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:

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

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:

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.

«« 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;
}
```

I went with double and int here to stick with primitive types; however, more appropriate (exact) data types can be used.

For example, Decimal (e.g., java.math.BigDecimal in Java), in some programming languages, might be more desirable to use than double which is an approximation of a real number and is discouraged for use in precise values like currency, which we are dealing with here. The primitive types, however, are more portable to other environments and so should be easier to follow.

The use of int for type is another consideration as with simple 0 and 1 this could be a boolean or bit. Consequently, it is always good to code for future improvement / enhancement, so using an int (or tinyint in some languages) allows for growth in the implementation without affecting the public interface. You can even get creative using binary bit flags to combine options (read: Binary Bit Flags: Tutorial and Usage Tips EE article). The point is, you can certainly code this using boolean for type, but I would suggest not or using a

«« try re-coding logic (leaving the pmt

«« Does it have any advantages aside from precision?

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):

Mathematically equivalent is this formula:

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);
}
```

or (shorthand)...

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

The above would be placed

«« do you see any potential issues with this code (hint: try values < 0)?

FV() Function

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

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:

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 :

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.

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:

«« 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;
}
```

«« write some test code to ensure you get same responses in Java as you do in Calc/Excel.

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

refresher on type impact on c formula.

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).

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

The above would be placed

«« run your test code using type set to 1.

IPMT() Function

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:

My take on IPMT() is it is the simple interest of the future balance of the period

or

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:

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

«« try the formula =FV(B1/B2,B3-1,B9,B5,B7)*B

```
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;
}
```

Very simple even if we choose to run through formula here, but always good to reuse code as that is the gift that is modular programming.

PPMT() Function

or

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

You will see that using formula in ...

Cell {B12}

or Cell {B13}

... 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);
}
```

«« try coding this using formula: c + (c * ((1 + r)^(per-1) - 1) / r + pv * (1 + r)^(per-1)) * r; substituting c with its appropriate formula.

Additional Programming Concepts

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) {
// ...
}
}
```

Note the modifiers 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.

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

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);
}
```

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

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.

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

Database-Specific Concepts

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/

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

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)
```

MySQL, MS SQL, et al:```
case type when 1 then 1 + r else 1 end
```

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 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.

«« 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.

Is there a float data type, maybe ?! (info:

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

Thank you again for reading...

Happy coding!

Best regards,

Kevin (aka MWVisa1)

```
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);
}
}
```

(info: dev.mysql.com/doc/refman/5

```
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 ;
```

(info: msdn.microsoft.com/en-us/l

```
-- 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
```

»

»

»

»

»

»

»

»

Ask questions about what you read

If you have a question about something within an article, you can receive help directly from the article author. Experts Exchange article authors are available to answer questions and further the discussion.