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_calcu lator
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;
}
if (type == 1) {
pmt = pmt / (1 + r);
}
if (type == 1) pmt /= (1 + r);
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;
}
if (type == 1) {
c = c * (1 + r);
}
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;
}
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);
}
public class Financials {
public static double pmt(double r, int nper, double pv, double fv, int type) {
// ...
}
}
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.
public static double pmt(double r, int nper, double pv, double fv) {
return pmt(r, nper, pv, fv, 0);
}
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.
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);
}
}
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 ;
-- 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
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.
Have a question about something in this article? You can receive help directly from the article author. Sign up for a free trial to get started.
Comments (22)
Author
Commented:Open in new window
In case I made a mistake in my derivation, remember that you simply solve the following for pv.
Open in new window
I hope that helps!
Respectfully yours,
Kevin
Commented:
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.
Commented:
Commented:
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.
Commented:
how to calculate excel financial formula " RATE" OR APR? can you tell me what is the algorithm of this formula?
View More