Mortgage Tax Deduction Formula Help

[skinny]

Hi,

I'm working with a mortgage calculator and am desperately trying to figure out a Mortgage Interest Tax Deduction Formula. The client is using Excel's "IPMT()" function. Anyone out there know what the formula behind that function is?

 

The formula I'm using currently (which produces different numbers than Excel's IPMT() function) is as follows:

TheTaxDeduction = ((TotalMonthPay * AnnualInterestRate) * 12)*35%

 

Any help will be greatly appreciated!

[Mark Baker]

Code used for the IPMT() function in PHPExcel

public static function PMT($rate = 0, $nper = 0, $pv = 0, $fv = 0, $type = 0) {
$rate	= self::flattenSingleValue($rate);
$nper	= self::flattenSingleValue($nper);
$pv		= self::flattenSingleValue($pv);
$fv		= self::flattenSingleValue($fv);
$type	= self::flattenSingleValue($type);

// Validate parameters
if ($type != 0 && $type != 1) {
	return self::$_errorCodes['num'];
}

// Calculate
if (!is_null($rate) && $rate != 0) {
	return (-$fv - $pv * pow(1 + $rate, $nper)) / (1 + $rate * $type) / ((pow(1 + $rate, $nper) - 1) / $rate);
} else {
	return (-$pv - $fv) / $nper;
}
}	//	function PMT()

private static function _interestAndPrincipal($rate=0, $per=0, $nper=0, $pv=0, $fv=0, $type=0) {
$pmt = self::PMT($rate, $nper, $pv, $fv, $type);
$capital = $pv;
for ($i = 1; $i<= $per; ++$i) {
	$interest = ($type && $i == 1)? 0 : -$capital * $rate;
	$principal = $pmt - $interest;
	$capital += $principal;
}
return array($interest, $principal);
}	//	function _interestAndPrincipal()

public static function IPMT($rate, $per, $nper, $pv, $fv = 0, $type = 0) {
$rate	= self::flattenSingleValue($rate);
$per	= (int) self::flattenSingleValue($per);
$nper	= (int) self::flattenSingleValue($nper);
$pv		= self::flattenSingleValue($pv);
$fv		= self::flattenSingleValue($fv);
$type	= (int) self::flattenSingleValue($type);

// Validate parameters
if ($type != 0 && $type != 1) {
	return self::$_errorCodes['num'];
}
if ($per <= 0 || $per > $nper) {
	return self::$_errorCodes['value'];
}

// Calculate
$interestAndPrincipal = self::_interestAndPrincipal($rate, $per, $nper, $pv, $fv, $type);
return $interestAndPrincipal[0];
}	//	function IPMT()

[skinny]

Hi Mark,

Thank you for the help!!

 

I'm trying to implement the code and have one question. It seems I'm missing the "flattensinglevalue()" function. I'm getting this error when trying to run: Fatal error: Call to undefined method PHPExcel::flattensinglevalue()

 

The code I'm using to run it:

<?php
class PHPExcel {

private static function _interestAndPrincipal($rate=0, $per=0, $nper=0, $pv=0, $fv=0, $type=0) {
	$pmt = self::PMT($rate, $nper, $pv, $fv, $type);
	$capital = $pv;
	for ($i = 1; $i<= $per; ++$i) {
		$interest = ($type && $i == 1)? 0 : -$capital * $rate;
		$principal = $pmt - $interest;
		$capital += $principal;
	}
	return array($interest, $principal);
}	//	

public static function IPMT($rate, $per, $nper, $pv, $fv = 0, $type = 0) {
	$rate	= self::flattenSingleValue($rate);
	$per	= (int) self::flattenSingleValue($per);
	$nper	= (int) self::flattenSingleValue($nper);
	$pv		= self::flattenSingleValue($pv);
	$fv		= self::flattenSingleValue($fv);
	$type	= (int) self::flattenSingleValue($type);

	// Validate parameters
	if ($type != 0 && $type != 1) {
		return self::$_errorCodes['num'];
	}
	if ($per <= 0 || $per > $nper) {
		return self::$_errorCodes['value'];
	}

	// Calculate
	$interestAndPrincipal = self::_interestAndPrincipal($rate, $per, $nper, $pv, $fv, $type);
	return $interestAndPrincipal[0];
}	//	
}
$c = new PHPExcel;
$c->IPMT(0.0538,1,360,1073000,0);

?>

Is it that I'm just missing that function or is it something else?

 

Thanks!!!!

[Mark Baker]

Yes, you probably wouldn't want to see then entirety of PHPExcel posted here. If you do want to download the entire script, it's available from the PHPExcel website, and provides the a calculation engine capable of handling the majority of Excel's function library (all bar 78 out of 352), as well as complex formulae (even without needing to read or write Excel workbooks).

 

All self::flattenSingleValue() does is test for an array rather than a simple value input, and reduces it to a simple value.

class PHPExcel_Calculation_Functions {

public static function PMT($rate = 0, $nper = 0, $pv = 0, $fv = 0, $type = 0) {
	// Validate parameters
	if ($type != 0 && $type != 1) {
		return '#NUM!';
	}

	// Calculate
	if (!is_null($rate) && $rate != 0) {
		return (-$fv - $pv * pow(1 + $rate, $nper)) / (1 + $rate * $type) / ((pow(1 + $rate, $nper) - 1) / $rate);
	} else {
		return (-$pv - $fv) / $nper;
	}
}	//	function PMT()

private static function _interestAndPrincipal($rate=0, $per=0, $nper=0, $pv=0, $fv=0, $type=0) {
	$pmt = self::PMT($rate, $nper, $pv, $fv, $type);
	$capital = $pv;
	for ($i = 1; $i<= $per; ++$i) {
		$interest = ($type && $i == 1)? 0 : -$capital * $rate;
		$principal = $pmt - $interest;
		$capital += $principal;
	}
	return array($interest, $principal);
}	//

public static function IPMT($rate, $per, $nper, $pv, $fv = 0, $type = 0) {
	$per	= (int) $per;
	$nper	= (int) $nper;
	$type	= (int) $type;

	// Validate parameters
	if ($type != 0 && $type != 1) {
		return '#NUM!';
	}
	if ($per <= 0 || $per > $nper) {
		return '#VALUE!';
	}

	// Calculate
	$interestAndPrincipal = self::_interestAndPrincipal($rate, $per, $nper, $pv, $fv, $type);
	return $interestAndPrincipal[0];
}	//	function IPMT()
}

$ipmt = PHPExcel_Calculation_Functions::IPMT(0.0538,1,360,1073000,0);

echo $ipmt;

 

Result is -57727.4

Excel 2007 gives -£57,727.40

 

[skinny]

Thank you crazy much for your help. You put me on the right track which allowed me to find another class for re-creating IPMT():

http://www.phpclasses.org/browse/package/892.html

 

Thanks again!!