# Interest amortization

#### brandonmcg

##### New Member
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!



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


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

### Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.
Use the CUMIPMT function

\$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.

Last edited:
"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

Where is the 41 and 44 coming from?

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

Replies
6
Views
762
Replies
1
Views
259
Replies
7
Views
392
Replies
1
Views
859
Replies
0
Views
1K

1,207,387
Messages
6,078,197
Members
446,320
Latest member
vatra

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back