<

Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x

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

Published on
81,037 Points
70,037 Views
10 Endorsements
Last Modified:
Awarded

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.

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

Preface
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/Fixed_rate_mortgage) for a greater understanding.  This guide will go through the algorithm(s) for determining the formula of each, along with explanations on the nuances of the implementations of these methods within Excel or Calc (spreadsheets).

For all readers, there is a GLOSSARY of terms AT THE END OF THIS ARTICLE that you can refer to if you get to a term that is emphasized (e.g., bolded and italicized text) but unfamiliar to you.

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

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.

So let's get started...

Contents
Algorithm: PMT() Function
Algorithm: FV() Function
Algorithm: IPMT() Function
Algorithm: PPMT() Function
Development: Additional Programming Concepts
Development: Database-Specific Concepts
Summary
Bonus Sections
Appendix A: Java
Appendix B: MySQL
Appendix C: MS SQL
Glossary

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:
PMT, FV, IPMT and PPMT Spreadsheet Illustration
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 :

   =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?


Code Example: payment using pv and fv.
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;
}

Open in new window


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 method overload to be discussed later.

Checkpoint:
«« try re-coding logic (leaving the pmt method signature "as-is") using java.lang.BigDecimal.  
«« Does it have any advantages aside from precision?


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

Code Example: additional logic to adjust payment based on (loan) type.
if (type == 1) {
   pmt = pmt / (1 + r);
}

Open in new window


or (shorthand)...
if (type == 1) pmt /= (1 + r);

Open in new window


The above would be placed after the calculation of the pmt variable from previous code sample.

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


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:
PMT, FV, IPMT and PPMT Spreadsheet IllustrationCell {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}).


Code Example: calculating fv.
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;
}

Open in new window


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


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)    

Code Example: additional logic to adjust fv based on (loan) type.
if (type == 1) {
   c = c * (1 + r);
}

Open in new window


The above would be placed before the calculation of the fv variable from previous code sample.

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


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:
PMT, FV, IPMT and PPMT Spreadsheet Illustration«« 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.


Code Example: calculating ipmt.
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;
}

Open in new window


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.

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.
PMT, FV, IPMT and PPMT Spreadsheet Illustration
Code Example: calculating ppmt.
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);
}

Open in new window



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


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

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

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)

Open in new window

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

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

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 ;

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

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.
10
Comment
Author:Kevin Cross
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 10
  • 4
  • 3
  • +4
22 Comments
 
LVL 1

Expert Comment

by:biolgb
Hi Kevin,
First of all I wanted to thank you for creating this knowledgebase article and I have found it very useful and nicely done, very informative.

I am just having trouble with the fv formula since it is not matching the proper results as follows;

Example A: My Actual info
FV(.05/12,1,10327.65, 235407.36,0);
Result: -246,715.87384306
Expected: -226,060.58

Example B: Based on your checkpoint spreadsheet
FV(.07/12,24,-1097.75, 165000,0);
Result: -189,718.16362685
Expected: -161,526.66

Using:
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;
}

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

Could you help me find out what is wrong? I am trying to play on the formula's priority order by grouping the values but I am still not getting the right results. Please help.
0
 
LVL 60

Author Comment

by:Kevin Cross
Thanks for reading, I am glad that you found this helpful.

Just remember my description above about cash flow (direction money moves).  If you have a positive principal value and then a positive payment value then then cash is moving in the same direction which would be indicative of a savings account, so the result you are getting makes sense as that is the result of compounded interest after n periods.  Now take a look with:

FV(0.05/12,1,10327.65, -235407.36, 0);
or
FV(0.05/12,1,-10327.65, 235407.36, 0);

Do you get the results you expect now? ;)

Based on your expected results being less than the principal value, I would say one of the two above is more indicative of what you are trying to work out as the money is flowing in different directions as if you have a loan that is being repaid and you are finding the balance after n payments.

I would double check your example B as I get the expected result.

Hope that helps.

Best regards,
Kevin
0
 
LVL 1

Expert Comment

by:biolgb
Hi Kevin,
Thanks for the reply, prompt as usual. I am quite a good software design engineer and developer but you may now know that I am a dummy when it comes to complex equations. It's a good thing we have people like you in the world that can cross the borders between the mathematical and logical dimensions.

Since I really needed this functions for a financial system I am developing right now, I already tried doing the math and working back to discover what was wrong. But of course your are the genius here. :-)

To let you see what I am doing here are the sample functions I have already created; (1) PMT, (2) FV, (3) IPMT and (4) PPMT versions for MySQL.

I based them all from the code you have provided. But as I have tried doing the checkpoint, I have noticed that when I changed the payment number to 2 in the Excel worksheet, IPMT and PPMT fails but PMT works just fine. Working on it some. I have seen that Fv, being used by both IPMT and PPMT, is giving different values. Could help me check it?

I will be working with you as you check it and provide you what I also could find.

Kindest regards,
George
>>>>>>>>>>>>>>>>>> PMT
DELIMITER $$
DROP FUNCTION IF EXISTS `C245037_InterAct`.`PMT` $$
CREATE DEFINER=`C245037_biolgb`@`%` FUNCTION `PMT`(
      Rate double,
      Nper double,
      Pv double,
      mFv double,
      mType int
     ) RETURNS double
BEGIN

  Declare mVal double;


Select Rate/(pow(1 + Rate, nPer) - 1)
       * -(Pv * pow(1 + Rate, nPer) + mFv) into mVal;

if mType = 1 then
   set mVal = mVal/(1 + Rate);
END IF;

return mVal;

END $$
DELIMITER ;

>>>>>>>>>>>>>>>>>> FV
DELIMITER $$

DROP FUNCTION IF EXISTS `C245037_InterAct`.`Fv` $$
CREATE DEFINER=`C245037_biolgb`@`%` FUNCTION `Fv`(
    Rate Double,
    nPer Double,
    mPmt Double,
    Pv Double,
    mType Integer
  ) RETURNS double
BEGIN

  Declare mVal double;

Select -(Rate * (pow(1 + Rate, nPer) - 1) / Rate + Pv
       * pow(1 + Rate, nPer)) into mVal;

if mType = 1 then
   set mVal = mVal * (1 + Rate);
END IF;

Return mVal;

END $$

DELIMITER ;

>>>>>>>>>>>>>>>>>> IPMT
DELIMITER $$

DROP FUNCTION IF EXISTS `C245037_InterAct`.`IPMT` $$
CREATE DEFINER=`C245037_biolgb`@`%` FUNCTION `IPMT`(
      Rate Double,
      Per INT,
      nPer INT,
      Pv Double,
      mFv Double,
      mType INT
      ) RETURNS double
BEGIN

Declare mVal Double;

SELECT Fv(Rate, Per - 1, pmt(Rate, nPer, Pv, mFv, mType), Pv, mType) * Rate into mVal;

if mType = 1 then
   set mVal = mVal/(1 + Rate);
END IF;

return mVal;

END $$

DELIMITER ;

>>>>>>>>>>>>>>>>>> PPMT
DELIMITER $$

DROP FUNCTION IF EXISTS `C245037_InterAct`.`PPMT` $$
CREATE DEFINER=`C245037_biolgb`@`%` FUNCTION `PPMT`(
      Rate Double,
      Per INT,
      nPer INT,
      Pv Double,
      mFv Double,
      mType INT
      ) RETURNS double
BEGIN

Declare mVal Double;

SELECT pmt(Rate, nPer, Pv, mFv, mType) -
       ipmt(Rate, Per, nPer, Pv, mFv, mType) into mVal;

if mType = 1 then
   set mVal = mVal/(1 + Rate);
END IF;

return mVal;

END $$

DELIMITER ;

Open in new window

PMT-FV-IPMT-PPMT.xls
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 60

Author Comment

by:Kevin Cross
No worries and thanks again for the great comments.

In case this gets a bit more involved, do you mind creating another thread in the question forum for MySQL:
http://www.experts-exchange.com/Database/MySQL/

This will allow other experts in that arena to share their thoughts as well.  I will begin looking at this though.

Thanks,
Kevin
0
 
LVL 1

Expert Comment

by:biolgb
Hi Kevin,
I have created thread "MySQL Custom Financial Functions (PMT, FV, IPMT, PPMT and others)".

Thanks,
George
0
 
LVL 93

Expert Comment

by:Patrick Matthews
Kevin,

Super article!  Some comments:

1) VB6 and VBA fully implements the PMT, FV, PPMT, and IPMT functions, so they are not just Excel/Access phenomena.  (Being a .Net ignoramus for the time being, I do not know off-hand whether VB.Net has them too.)  I understand that your focus was on Java and SQL, of course

2) I've been struggling with how to write a PPMT/IPMT formula that would return the Nth principal or interest payment for a loan where you make additional payments to principal.  For example:

Principal: $100,000
Years: 30
Annual rate: 10%
Accrued monthly

The monthly payment would be $877.57:
=-PMT(Rate/12, Years*12, Principal)

The components of the 5th payment:
Principal: $45.73               =-PPMT(Rate/12, PmtNumber, Years*12, Principal)
Interest: $831.84             =-IPMT(Rate/12, PmtNumber, Years*12, Principal)

If you made, say, and extra $200 payment to principal each month, then the monthly payment becomes $1,077.57, and the 5th payment is $252.48 principal and $825.09 interest, but I cannot think of a formula to determine that directly.  Instead, all I can think of is to create the amortization schedule and grab the 5th payment.

Any ideas?  :)

Cheers,

Patrick
0
 
LVL 60

Author Comment

by:Kevin Cross
Thanks for the comments, Patrick.  Good point on VB6 and VBA information.  I will check next time I am in VB.NET if it has these functions, but yes was focusing in on databases and languages that didn't based on question I had here on EE.

As to your question regarding IPMT and PPMT:
(please pardon my ignorance if there is already a quick formula for this)

I would think the formula would involve some sort of FV calculation with the $200.00.  Since payment 5 would have had 4 extra payments of $200.00 applied OR PmtNumber - 1, then if my logic is right it is like starting with a balance of $0.00 in a savings account that you add $200.00 each month with compounded interest monthly for (PmtNumber - 1) months.  The trick then is to find out how much your $800.00, in your example, is actually worth (FV) by the nth payment and subtract that from the principal before calculating IPMT and PPMT.

Very intriguing thought since I do a fair bit of this with my mortgage and car payments to try to lessen my interest -- I have even split my payment in two and sent half way between last payment and next payment since some banks calculate interest between payments and not monthly.   Or probably more appropriately since I believe all go payment to payment, but some banks will take an early payment and save it until the due date to apply it so you can send three checks and they would all get applied like above versus some banks that would benefit you since payments 2 and 3 for the month would include interest on lower principal amount even though in total you are only paying you normal payment amount each month.  Maybe this only really works with car loans since the term makes it that even half payment is enough to covert interest for half-a-month versus a mortgage which could be 95% interest.

Anyway, I digress.  

As I said, I am very intrigued; therefore, I will probably actual play with the formula and see if any better ideas pop-out or refinement to my theory above if it pans out.

Best regards,

Kevin
0
 
LVL 60

Author Comment

by:Kevin Cross
Patrick, this is probably the long way around and may not be right as I don't get the results you do; however, I had a chance to test my thoughts and seems like it gets close that there is something there.
The basic idea is to find the compound interest you are saving by making the extra payments.  Since $200.00 is also being added to balance each month, I didn't suspect an easily formula for interest would do; therefore, I used FV() to calculate the future value of extra payments and then subtracted the extra payments themselves.

i.e., FV(Rate/12, 4, 200, 0)+800

Where 4 is payment number minus 1 and 800 is the total of the 4 extra payments.  So subtracting this result from IPMT() I get reduced interest amount of $821.78; adding this to PPMT along with $200.00, gets increased principal portion of $255.79.

Again very intriguing thought for those of us who make extra payments.

Thanks also, as it made me realize that, though, I tested all the function codes, on my bonus code snippet I added an overloaded version of FV() that doesn't require the type parameter; however, I didn't include a call to the original method with 0 for type but instead kept calling the overload.  The corrected code is in the attached.

Thanks again for reading.
/**
 * 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, 0);
}

Open in new window

0
 
LVL 60

Author Comment

by:Kevin Cross
In the world of mortgages, 0.00% is an unlikely event; however, our functions should account for this as (1) PMT() and associated functions can be used for other loans that can be 0.00% APR and (2) just good practice to handle exceptional cases gracefully.

With that said, the PMT() and FV() functions would be impacted that payment would simply be even split of principal + future value over number of payments while the future value would simply remain the principal.  

An updated version of Java Financials utility class has been attached as an example, but the methodology used to account for zero should be easily transferred to other languages like SQL.

Thanks again for reading.

--Kevin
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) {
		if (r == 0) return -(pv + fv)/nper;
		
		// i.e., 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) {
		if (r==0) return pv;
		
		// 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, 0);
	}

	/**
	 * 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);
	}
}

Open in new window

0
 
LVL 93

Expert Comment

by:Patrick Matthews
Kevin,

Your article has inspired me to update my VBA/VB6 UDFs creating amortization schedules for a future EE article.  The originals were posted here some time ago: http://vbaexpress.com/kb/getarticle.php?kb_id=833

Of course, those functions have significant limitations:

1) Neither allow for a future value balance at the end

2) While AmortSchedTraditional allows for an additional payment to principal, that additional payment is applied for the duration of the schedule, and always at the same amount (except potentially for the very last payment, of course, as the amount of principal paid, schedule due or additional, cannot exceed the loan balance)

I am modifying both UDFs to allow for FV arguments, and I am modifying the AmortSchedTraditional function to allow for a full schedule of additional principal payments.  For example, you could specify "do an extra $100 for payments 1-15 but stop after that", or "do an extra $200 for payments 1-12, $100 for 13-24, and $50 thereafter", etc.  A ParamArray defining the additional principal schedule can cope with that pretty easily.

I'll also do versions of PPMT and IPMT that allow for additional principal payments.

A question I would love you to weigh in on: what to do about additional payments to principal when there is a future value specified?  For example, a $100,000 loan for 30 years with $20,000 FV.  If additional payments are specified, then well before the last payment the outstanding balance will hit the $20,000 FV.  I could allow the amortization schedule to extend the full 30 years and have it include interest-only payments for a while, or I could allow the additional payments to eat into the FV, thus leaving the final balance of the loan at < $20,000 (or even fully paid off, potentially).

I suppose I could have an argument specify that behavior too, or make it part of how the additional payments are specified.  Thoughts?

Patrick
0
 
LVL 60

Author Comment

by:Kevin Cross
Patrick:

As usual, very interesting.  I will eagerly be awaiting the article on the AmortSchedTraditional function.

This is definitely tricky though as the payment amount and thus the values used to determine monthly interest/principal paid need to be based on original loan term of 30 years, but the amortization table will have to stop once its threshold (balloon amount) is reached -- however, changing the rules by adding additional payments that can be consistent or scattered for x number of payments as you indicated, makes the amortization schedule less predictable.

I tried explaining the PPMT and IPMT portion here - http:#c8150 - and think that takes care of FV amount as that was included in PMT calculation to begin with; therefore, the only think you are looking for in the loop to create amortization table is if the current remaining balance is less than the desired FV + one PMT (and maybe additional payment if regular or scheduled for next payment).  If so, you have reached pay-off or balloon payment point.

You can then exit the loop and thus cut your amortization shorter than 30 years, which will show the savings of making extra payments visually by seeing shorter length of time in addition to the facts of the lower amounts of interest / total payments.

A parameter for this would work also which basically sets the balloon to zero allowing you to see the amortization through the zero again to parallel the advantages of paying extra payments that not only do you save interest, but you pay-off more of the principal, having a smaller FV at payment n than you would have previously.

Hope that helps some.  Not sure if my thoughts are on par with your brilliance, but seems to pop up in my world a bit also.  Had another Java question on this today. :)

Regards,

Kevin
0
 
LVL 60

Author Comment

by:Kevin Cross
I knew that was a great question.

I thought some more about this and on payment n if you look at the principal balance you will really have to look at if it is < 20,000 + PPMT (principal portion of payment n+1 including additional payment) ; therefore, you could get into if the current balance is > 20,000 (your desired FV) and you are not on last payment then check to seek the next principal amount and subtract it to determine if that is < than the 20,000+PPMT(n+1).  This will tell you that you only have 1 more payment left or if you keep looping as normal.  

As I said, logically, you can make the parameter on how to handle intruding on FV by simply using it to determine if FV is changed to 0 before the calculation is performed.

Thinking of this in another manner, you could do this test after you reach payment n+1 instead of a seek, but hopefully that makes sense.

Thanks for all the thought provoking comments.
0
 
LVL 50

Expert Comment

by:Dave Brett
You could try
= -IMPT(10%/12,5,360,100000+PV(10%/12,4,200))
= 825.32

ie deduct the Present Value of the future 4 monthly payments of 200 directly from the upfront principal of 1000000

which is close to the 825.09 in my amortisation table (which ties to you calc Patrick)

The very small difference is interesting as if I plug the starting balance as
100000+PV(10%/12,4,200)
I get a starting balance of 99,216.39 which at the start of period 5 is  99,010.77 - indentical to the starting balance I get  for using 100000 + 4 monthly payments if 200. So the manual amortisation calc says these two approaches are identical, but the IPMT is ever so slightly out

Great discussion in a great article

Cheers

Dave
0
 
LVL 93

Expert Comment

by:Patrick Matthews
Kevin,

I finally got around to writing that article: http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/A_3331-Fixed-Rate-Loan-Amortization-Schedule-with-Optional-Extra-Principal-Payments.html

Just submitted, so it has not gone through editorial review as of yet.

Cheers,

Patrick
0
 
LVL 60

Author Comment

by:Kevin Cross
Looks pretty good, Patrick. I will have to read this one tonight! Thanks for sharing the link.

Kevin
0
 

Expert Comment

by:techneeq
Kevin,

Fantastic write up and unbelievable detail. Thank you!

Instead of PMing with a question, I thought it would make sense to post it here.  Did you create (or could reference) a Present Value (PV) formula in t-SQL?  We are trying to duplicate the Excel - PV() formula in sql or oracle and I have not found a reliable source with this logic.  If you have not already created one, do you have any resources that could at least get me started?

Many thanks.
Tech
0
 
LVL 60

Author Comment

by:Kevin Cross
Thank you!

Regarding the Present Value implementation, I can post something in the next few days. I am recovering from a medical procedure; therefore, it will take me a couple days. If you need assistance sooner, please post a question with a link to this article. I have posted the PV formula and caveats for T-SQL.

Best regards,

Kevin
0
 
LVL 60

Author Comment

by:Kevin Cross
I have not fully put this function through the paces, but I hope it serves as a good example. Caveat emptor, data types make a difference in some functions such as POWER(); therefore, some of the answers may not precisely match with Excel due to precision/rounding issues.

-- optionally create a utility schema for Financial functions.
create schema financials;
go

-- drop existing PV() function.
if exists(
   select 1
   from sys.objects 
   where object_id = OBJECT_ID(N'[financials].[pv]') 
      and type in (N'FN', N'IF', N'TF', N'FS', N'FT')
) drop function [financials].[pv];
go

-- create new PV() function with signature:
-- PV( interest_rate, number_payments_remaining, payment_amount, future_value, loan_type )
create function [financials].[pv] (
   @r decimal(38,36),
   @nper int,
   @pmt decimal(38,19),
   @fv decimal(38,19),
   @type tinyint
) returns decimal(38,19)
as
begin
   declare @pv decimal(38,19);
   
   /* 
   Adjust payment value based on loan type. 
   i.e., account for payment at beginning of period versus end. 
   */
   if (@type = 1) set @pmt = @pmt * (1 + @r);

   /* 
   Solve for present value. 
   */
   set @pv = @fv - @pmt * (1 - power(1 + @r, -@nper)) / @r;
   
   /* Return present value. */
   return @pv;
end

Open in new window


In case I made a mistake in my derivation, remember that you simply solve the following for pv.
fv = -(c * ((1 + r)^N - 1) / r + pv * (1 + r)^N) 

Open in new window


I hope that helps!

Respectfully yours,

Kevin
0
 

Expert Comment

by:Michael Bartosh
I was intrigued by your article and used it to implement a scalar function in MS SQL Server 2016. It had been working well until I ran into a problem when I received an error...

Msg 8134, Level 16, State 1, Line 44
Divide by zero error encountered.

I discovered the source of the error after evaluating all of my data that it occurs when the rate is equal to zero, In fact, with the current financial status being what it is, I have been running into more and more cases where a zero rate occurs.

I addressed the situation with a special case calculation in my SQL Server function where the Payment is equal to the (PV + (-FV)) / nper; where PV is present value (or funded amount) and FV is the future value (or residual amount at the end of  the term) and nper is the number of periods payments are required.

I guess the MS Excel programmers figured this out a long time ago because using the same values in an Excel spreadsheet with the PMT function yields the correct result.
0
 
LVL 93

Expert Comment

by:Patrick Matthews
If the discount rate is zero, then there is no different between the present value and future value.
0
 

Expert Comment

by:Michael Bartosh
Not true... On a lease contract, the future value is stated as the "residual".  The present value is the original cost (before payments, over the term of the contract, whether the discount rate is zero or some other value, are made). On a retail contract, the future value is zero, And the present value is the original cost (just like it is for the lease contract).

The discount rate will only tell you what amount of each payment is attributed to interest. On a discount rate of zero, the interest on each payment is zero.

Therefore,  for a  discount rate of zero; the PMT = ( PV - FV ) / nper  

Examples with a discount rate of zero include: 1) A lease with an original cost of $50000 and a future value of $14000 with a term of 3 years (36 monthly payments) = (50000 - 14000) / 36 = $1000 per month payment.  2) a retail contract with an original cost of $50000 will have a future value of $0, with the same 3 year term (36 monthly payments) = (50000 - 0) / 36 = $1,388.89 per month payment.
0
 

Expert Comment

by:Razib palash
#help
how to calculate excel financial formula " RATE" OR APR? can you tell me what is the algorithm of this formula?
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Join & Write a Comment

This is a video describing the growing solar energy use in Utah. This is a topic that greatly interests me and so I decided to produce a video about it.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month