IPMT: Investment interest calculated as negative value

[Equals43]

My scenario is that i have a 5 year investment with a PV of 50000,a FV of 10000, a 7% annual interest rate with which the payments are at the start of each period and i want to find the interest payments for years 1 through 5, given that it's an investment i expect the interest payments to be positive values. However whenever i calculate each year's payment with =IPMT(7%,X,5,50000,100000,1) with X being each year, i get=
$0.00, -$1,564.62,$261.23, $2,214.89, $4,305.31 from my function. Does the function assume that because there is a PV that it is automatically a loan hence the negative interest? As i find that removing the PV value gives me positive values as expected.

Cheers

[JeteMc]

Quoted from MS Office Support under remarks:
"For all the arguments, cash you pay out, such as deposits to savings, is represented by negative numbers; cash you receive, such as dividend checks, is represented by positive numbers."
My take is that the 50,000 is going out of your pocket and to the investment institution, as such it should be entered as a negative number.
I think that there is a Highline College You-Tube (Finance) video that covers the why's of this.
Normally I would say let me know if you have any questions, however this about taps my knowledge of the subject.

[joeu2004]

My scenario is that i have a 5 year investment with a PV of 50000,a FV of 10000, a 7% annual interest rate with which the payments are at the start of each period and i want to find the interest payments for years 1 through 5, given that it's an investment i expect the interest payments to be positive values. However whenever i calculate each year's payment with =IPMT(7%,X,5,50000,100000,1) with X being each year, i get [some negative results]

The simple answer is: the usage should be =IPMT(7%,A11,5,-50000,100000,1), where A11:A15 are the year numbers 1 through 5.

PV and FV should have opposite signs ("signed cash flows"). It is arbitrary which you call inflow and outflow and which sign you use for them, as long as you are consistent. I use negative values for investments (deposits), since they are paid "out" of pocket.

Even though that eliminates the negative results in your example, I do not believe IPMT gives you the correct numbers.

This is demonstrated by the following table, explained below. I believe you should use the formulas in column G.

	A	B	C	D	E	F	G
1	pv	-$50,000.00
2	rate	7.00%
3	nper	5
4	pmt	-$4,854.71
5	fv	$100,000.00
6	type	1	in advance
7	total int	$25,726.47
8
9		beg pmt	beg bal	int	end bal	ipmt	fv()-fv()
10	init				$50,000.00
11	1	$4,854.71	$54,854.71	$3,839.83	$58,694.53	$0.00	$3,839.83
12	2	$4,854.71	$63,549.24	$4,448.45	$67,997.69	$3,839.83	$4,448.45
13	3	$4,854.71	$72,852.39	$5,099.67	$77,952.06	$4,448.45	$5,099.67
14	4	$4,854.71	$82,806.77	$5,796.47	$88,603.24	$5,099.67	$5,796.47
15	5	$4,854.71	$93,457.94	$6,542.06	$100,000.00	$5,796.47	$6,542.06
16	total	$24,273.53		$25,726.47		$19,184.42	$25,726.47

Please Login or Register  to view this content.

Aside.... Usually, I ensure that financial formulas and numbers in tables are positive values. Then I add or subtract them according to the intended direction of the cash flow. That makes the formulas more intuitive, IMHO. However, for this example, I use negative values in order to distinguish outflows, since the concept appears to be new to you.

Note that F16, the sum of IPMT results, is different from B7 and D16 (total interest). But G16 is the same.

Also note that F11 is zero, the first-period interest. That seems counter-intuitive, especially for an investment.

IPMT seems to assume that interest is also calculated at the beginning of the period, based on the previous ending balance. Arguably, that might be correct for debts that are paid "in advance" (e.g. leases). That is reflected in some online tutorials.

I don't agree. And neither does the implementation of the FV function.

But in any case, for investments, we want to calculate and add interest at the end of the period, even if payments (additional invested funds) are paid at the beginning. That is reflected in the formulas in columns D and E.