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.

 

[Pumper]

Hi Dave, I checked against an online calculator but found a better one and matches you!

Currently working through what you provided trying to get it to work., bit lost using goal seek but will read up on how to use.

Appreciate your help with this, you make it look so straight forward...

 

[Dave Patton]

with a few guesses, and/or interpolation, or goal seek you can get to a reasonable whole number of payments

Amortization.xlsm
	F	G	H
3	$4,796.40	$5,315.32	$518.92
4	$4,796.40	$5,272.62	$476.22
5	$4,796.40	$5,298.03	$501.63
1c

Cell Formulas
Range	Formula
G3	G3	=PMT(C2,280,-B5,0)
H3:H5	H3	=G3-F3
G4	G4	=PMT(C2,285,-B5,0)
G5	G5	=PMT(C2,282,-B5,0)

 

[Pumper]

Thanks Dave, think i am going to set up amortization tables, was trying to avoid this as the spreadsheet can hold up to 10 properties with loans going out to 30yrs but seems unavoidable, thought I was there with your help yesterday only to remember the option of additional payments

I have learnt a few things from your help so thank you again!

 

[Dave Patton]

You do not need to use Goal Seek.
The nearest whole number is 282 so we calculate the amount of payment with a term of 282 months

Amortization.xlsm
	G
12	282.1903635
13	$5,298.03
1c

Cell Formulas
Range	Formula
G12	G12	=NPER(0.005,4796.4+500,-800000)
G13	G13	=PMT(0.005,282,-800000)

 

[Pumper]

Hi Dave, apologies for the late reply was flat out yesterday.
That is perfect, with all your help I managed to get it to work!

Thanks for your time and effort on this it has really helped

 

[Dave Patton]

Thanks for the feedback.