Excel - Mortgage Principal Paid over a Period

Interested in determining how much principal is paid on a mortgage between two time periods?


CUMPRINC(Rate, nper, pv, start_period, end_period, type)

Example: How much principal will a borrower pay between their 10 and 15 payment on their mortgage?  Principal balance at the starting period is $51000 and interest rate in 9%.

Rate - it's the interest rate for the period, so for a mortgage with 9% you need to divide by 12 to get the monthly interest rate, .09/12

nper - total number of payments to be included, so the number of months between the start period and end period  (15-10), 5

pv - the principal at the starting period

Start_Period - Payment date doesn't work, you have to figure out the payment number, i.e. the 10th payment out of the term. 10

End_Period - Again, not a date, it has to be the number of the last payment, 15

Type - set it to 0 if the payment is made on the first of the month, 1 of the payment is on the end of the month

=CumPrinc( .09/12, 5, 51000, 10, 15)

Cumulative Principal Paid Between Periods

Excel - Countifs Using Equal To or Greater Than with a Date Field

There's a wrinkle in the countifs() formula for using a date field rather than an explicitly declared date in the formula.

Here's an example:
=COUNTIFS($P$15:$P$2050,"91+",$D$15:$D$2050,$A$1,$Q$15:$Q$2050,">=1/10/2013")

I wanted to replace the "1/10/2013" with a date field in A3.

My first attempt was this:
=COUNTIFS($P$15:$P$2050,"91+",$D$15:$D$2050,$A$1,$Q$15:$Q$2050,">=$A$3")
Which doesn't work.  My next try was this:

=COUNTIFS($P$15:$P$2050,"91+",$D$15:$D$2050,$A$1,$Q$15:$Q$2050,">="$A$3)
Again, that didn't work.


Now here's the correct way to use a date field in Countifs():

=COUNTIFS($P$15:$P$2050,"91+",$D$15:$D$2050,$A$1,$Q$15:$Q$2050,">="&$A$3)

See that little ampersand between the greater than and equal section and the field?  That's the trick.


You're welcome.