Interest amortization

[brandonmcg]

If I have terms for a mortgage and I wanted to know the interest between two dates can I get that number without having the full amortization schedule shown? I have very limited space. $200,000 mortgage, 360 month term at an interest rate of 4%, First payment date 06/01/2015 and payment amount of $954.83. What would the sum of the interest be between any date? i.e. 10/1/2018 and 01/01/2019. I just need the sum of the interest for the interest between the two dates.

If I wanted to see what the unpaid principal balance would be at any given date within an amortization schedule is there an easy way to do that?

Sounds like too much work than just to have the am schedule and run the dates. If it is an option please let me know.

Thank you!

</SPAN>

<TBODY>
</TBODY><COLGROUP><COL></COLGROUP>

</SPAN>

<TBODY>
</TBODY><COLGROUP><COL></COLGROUP>

 

[sheetspread]

Use the CUMIPMT function

 

[joeu2004]

$200,000 mortgage, 360 month term at an interest rate of 4%, First payment date 06/01/2015 and payment amount of $954.83. What would the sum of the interest be between any date? i.e. 10/1/2018 and 01/01/2019.
[....] If I wanted to see what the unpaid principal balance would be at any given date within an amortization schedule is there an easy way to do that?

	A	B	C	D
1	Loan	200,000.00
2	Int Rate	4.00%	annually
3	Term	360	months
4	Pmt	954.83		B4: =ROUND(-PMT(B2/12,B3,B1),2)
5	1st Pmt	6/1/2015
6	Last Pmt	5/1/2045		B6: =EDATE(B5,B3-1)
7	Start Per	10/1/2018	41	C7: =DATEDIF($B$5,B7,"m")+1
8	End Per	1/1/2019	44	C8: =DATEDIF($B$5,B8,"m")+1
9	Cum Int	2,495.96		B9: =-CUMIPMT(B2/12,B3,B1,C7,C8,0)
10	End Bal	186,368.25		B10: =-FV(B2/12,C8,-B4,B1)

<tbody>
</tbody>

Note: CUMIPMT actually uses the unrounded payment PMT(B2/12,B3,B1). It makes little difference in the short-term. Over the entire term of the loan, the difference is about $0.20.

PS: Row 6 (last pmt date) is not required to answer your immediate questions. I just thought you might be interested.

 

[SpillerBD]

"Eight other functions began using “new algorithms” starting in Excel 2010. This means that a worksheet in Excel 2007
might return different answers from a worksheet in Excel 2013. These improved algorithms often affect only fringe cases
of the functions. For normal usage, the results are usually the same. However, if Excel 2013 returns a different result, it
is more accurate than the Excel 2007 result. Here are the functions affected:" Bill Jelen, Excel 2013 In Depth.
That includes CUMIPMT & CUMPRINC

 

[brandonmcg]

Where is the 41 and 44 coming from?

 

[sheetspread]

The formula next to it describes: # of months between 6/1/15 and 10/1/18 + 1 (number of periods)