Amortization Schedule

[Pumper]

Hi All, I can see numerous posts on this but nothing quite what I am after.

I am trying to get back the yearly interest payment that is calculated based on monthly payments without having to set up amortization tables.

Is it possible to bring back the yearly interest payment using IMPT rate using the monthly calculations? from what I have come across it gets you close but not exact.

eg For a loan of $800,000 at 6% interest for 30yrs the first yearly amount should be $47,732.76 not $48,000,00 which is what I get using the IMPT formula.

If anyone could help out or point me in the right direction that would me much appreciated.

Trying to avoid doing like this as I have so many to do in the same spreadsheet.

 

[kweaver]

What about:

=SUM(IPMT(B3/B5,{1,2,3,4,5,6,7,8,9,10,11,12},B4*B5,B6))

 

[Dave Patton]

Amortization.xlsm
	A	B	C
1
2	Rate	6%
3	Term	30
4		12
5	Amount	$800,000.00
6
7	Interest Year 1	47,732.76
8	Payment	$4,796.40
1c

Cell Formulas
Range	Formula
B7	B7	=-CUMIPMT(B2/12,B3*B4,B5,1,12,0)
B8	B8	=PMT(B2/12,B3*B4,-B5,0,0)

 

[Pumper]

Thanks for your time kweaver that works, very kind of you to take a look, Dave think yours will be easier to use over multiple properties so thank you very much too! works fine but can I bother you to ask where you would change the formula for the second year? 47,126.82 would be the correct amount.

ie when i change the last 12 to 24 in your formula I get 90,860 then subtract the pervious 47,732.76 from that to get 43,127 which is correct but I assume there is a cleaner way to do?

If not I can work with what you kindly provided.

Thanks again for your time

 

[Dave Patton]

Amortization.xlsm
	C	D	E	F	G
6	Year 2	13	24	47,126.83	47,126.83
1c

Cell Formulas
Range	Formula
F6	F6	=-CUMIPMT(B2/12,B3*B4,B5,13,24,0)
G6	G6	=-CUMIPMT(B2/12,B3*B4,B5,D6,E6,0)

Try reading Excel's help information for the function.

 

[kweaver]

In my haste I forgot about CUMIPMT. Dave's is the better way.

BTW, @Pumper, you need 13 to 24 for the 2nd year, not 12 to 24, or 1 to 24 if you want two years cum interest.

 

[Pumper]

In my haste I forgot about CUMIPMT. Dave's is the better way.

BTW, @Pumper, you need 13 to 24 for the 2nd year, not 12 to 24, or 1 to 24 if you want two years cum interest.

Yep understood thanks very much!

 

[Pumper]

I have kindly been provided the following by Dave above which works perfectly however I also need to add the option to add additional monthly payments.

Is there a way to modify =-CUMIPMT(B2/12,B3*B4,B5,1,12,0) to incorporate say $500 additional monthly payments?

This would return $45,719,.38 for year 1 if $500 per month of additional payments was added.

Thanks in advance.

 

[Dave Patton]

With an extra payment of $500 per month, the loan would be paid off in approximately 282 payments.
With Goal seek, the amount of payment for 282 months is $5298.03.
If you do not have Let, you can edit the formula.

Amortization.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1								Interest by period year CUMIPMT
2	Rate	6%	0.005		Year 1	Year 2	Year 3	Year 4	Year 5	Year 6	Year 7	Year 8	Year 9	Year 10
3	Term	30			$47,564.43	$46,576.85	$45,528.35	$44,415.19	$43,233.37	$41,978.66	$40,646.56	$39,232.30	$37,730.81	$36,136.71
4		12
5	Amount	$800,000.00
6
7
1cc

Cell Formulas
Range	Formula
C2	C2	=B2/B4
E3	E3	=-CUMIPMT($C2,282,$B5,1,12,0)
F3:N3	F3	=LET(t,COLUMNS($H$1:H1)*12,-CUMIPMT($C2,282,$B5,t+1,t+12,0))

 

[Dave Patton]

I moved the data before the post.
The same information follows.
How did you calculate the amount of interest?

Amortization.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1
2	Rate	6%	0.005		Year 1	Year 2	Year 3	Year 4	Year 5	Year 6	Year 7	Year 8	Year 9	Year 10
3	Term	30			$47,564.43	$46,576.85	$45,528.35	$44,415.19	$43,233.37	$41,978.66	$40,646.56	$39,232.30	$37,730.81	$36,136.71
4		12			$47,564.43	$46,576.85	$45,528.35	$44,415.19	$43,233.37	$41,978.66	$40,646.56	$39,232.30	$37,730.81	$36,136.71
5	Amount	$800,000.00			$47,564.43	$46,576.85	$45,528.35	$44,415.19	$43,233.37	$41,978.66	$40,646.56	$39,232.30	$37,730.81	$36,136.71
6
7
8	Payment	$5,298.03	$501.63
1cc

Cell Formulas
Range	Formula
C2	C2	=B2/B4
E3	E3	=-CUMIPMT($C2,282,$B5,1,12,0)
F3:N3	F3	=LET(t,COLUMNS($F$1:F1)*12,-CUMIPMT($C2,282,$B5,t+1,t+12,0))
E4	E4	=-CUMIPMT($C2,282,$B5,1,12,0)
F4:N4	F4	=-CUMIPMT($C2,282,$B5,COLUMNS($F$1:F1)*12+1,COLUMNS($F$1:F1)*12+12,0)
E5	E5	=-CUMIPMT($C2,282,$B5,1,12,0)
F5:N5	F5	=-CUMIPMT($C2,282,$B5,COLUMNS($F$1:F1)*12+1,COLUMNS($F$1:G1)*12,0)
C8	C8	=B8-'1c'!B8