Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.
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.>>>>>>>>>>>>>>>>>> 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 ;
PMT-FV-IPMT-PPMT.xls
/**
* 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);
}
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);
}
}
-- 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
fv = -(c * ((1 + r)^N - 1) / r + pv * (1 + r)^N)