calculating Payment of the Principal (PPMT) starting with a partial month repayment in Excel or VBA

Question

I am trying to calculate the loan repayment schedule for a loan. however the process to get (and repay) a loan could look like this.

some details of loan:

{loanValue=10000, interest=0.1, termMonths=12};

1. Apply for loan on 6 jan -- but i specify I would like to repay End of Month.
2. Get funds on the 15th (effectively starting the loan)
3. I repay my first repayment on the 28th of Jan ( this is the specified EoM date)

I can easily work out the:

• Repayment amount: -PMT((1+0.1)^(1/12)-1,12,10000) $=877.16
• Capital portion: -PPMT((1+$B$2)^(1/12)-1,1,12,10000) = $797.41
• Interest portion``: -IPMT((1+$B$2)^(1/12)-1,1,12,10000) = $79.74

However, I am not sure how to adjust the first months repayment amount ( and effective calculations) to support that initial partial month?

The following is how far i got on a generic function for this:

Private Function createRepaymentSchedule(TotAmnt, anIntRate, nPER, startDate, processDate)
 Dim rs As DAO.Recordset
    Dim RT As Double
    Dim RES As Double
    RT = (1 + anIntRate) ^ (1 / 12) - 1
    Dim Sql As String
    Pval = TotAmnt
    For i = 1 To nPER
        cap = -PPmt(RT, i, nPER, TotAmnt)
        intr = -IPmt(RT, i, nPER, TotAmnt)
        Pval = Pval - (cap)
        Debug.Print (i & "~" & Round(cap, 2) & "~" & Round(intr, 2) & "~" & Round(Pval, 2))
    
    Next i

Answer 1

A lot of this depends on the loan terms and conditions. The answer below is based on some assumptions (that I would rather verify by comment in advance):

1. Continuous compounding
2. Payment on last working day of month after the first payment is treated the same as payment on last day of month
3. The Process Date is not used in any calculations, so I cannot incorporate it.
4. The Start Date is the effective loan origination date.
5. The loan termination date is the last day of the month nPer months after the Start Date

Normally PMT, PPMT, and IPMT are based on another assumption, which is almost never true in reality : payments are uniform. So each period is exactly the same duration, down to the second. The fact that one month is shorter than another and the real payment dates are on working days is usually not a an important factor, so PMT does a "good enough" approximation. However, your first payment date occurring in only half of a period, will make a material difference - especially to the PPMT and IPMT, so your point is correct. These cannot be used.

At first, I attempted a financial trick to answer the question: roll back the loan date to the start of the month and set the loan amount = the PV of the loan as of the real start date (-15 days). That works financially, but it creates a problem in the schedule of Principle, Interest and Capital. Rather than tweak it, I simply did what you proposed: build a new PPMT that is not only based on your start date, but also incorporating your "last weekday of month" requirement. The result is something that would match up to banking standards.

This could be built in VBA as you proposed, but I decided to stick with pure Excel as I am finding that lately the LET is an underutilized approach and solving it with LET would have its own merits. So, this solution is based the Excel LET function and it produces a Dynamic Array that will spill onto the worksheet. It does require Excel 2016 or Microsoft 365. If that is a problem, tell me in comments and I can convert this to VBA (but with some really different methods).

Set Up I put your key variables (TotAmt, anIntRate, nPer, startDate, and processDate) into cells B1 through B5. NB: processDate is not used. The results are placed in A8, with headers in A7:F7 to label each series of values.

To get started, I created two helper ranges that are also part of the output: Item and Date. I could have incorporated these into the formula, but I think it is better to expose them so that you can see the schedule.

Item is simple a vertical sequence =SEQUENCE( B3 + 1,,0 ). Date is a dynamic array based on a formula that computes the last working day of the month:

=IF( A8#, WORKDAY( EOMONTH( startDate, A8# - 1 ) + 1, -1 ),
             startDate )

NB: your last weekday is 28 Jan. You might be in a SUN-THU country. There is another way to compute last weekday that is based on WEEKDAY that would allow you to shift it to your country's schedule. If you need that, ask for it in the comments.

The Formula I put the following formula into C8:

=LET( loanAmt, B1,
      anInt, B2,
      nP, B3,
      startD, B4,
      pmtDates, B8#,
       v, SEQUENCE( nP+1,,0 ),   h, TRANSPOSE( v ),
       pp, SIGN( h ),
       ones, SIGN( TRANSPOSE( pp + 1 ) ),
       stagr, (v - h + 1) * (v >= h),
       dlyInt, ( 1 + anInt ) ^ (1/365 ) - 1,
       fvFactors, ( 1 + dlyInt ) ^ ( pmtDates - startD ),
       fvarray, INDEX( fvFactors, stagr, ) * SIGN( stagr ),
       guess1, PMT( ( 1 + anInt ) ^ (1/12 ) - 1, nP, loanAmt ),
       rseq1, MMULT( fvarray * ( loanAmt*(1-pp) + guess1*pp), ones),   guess2, guess1 * ( 1 + INDEX( rseq1, 13 )/loanAmt ),
       rseq2, MMULT( fvarray * (loanAmt*(1-pp) + guess2*pp), ones ),   guess3, guess2 * ( 1 + INDEX( rseq2, 13 )/loanAmt ),
       rseq3, MMULT( fvarray * (loanAmt*(1-pp) + guess3*pp), ones ),   guess4, ROUND( guess3 * ( 1 + INDEX( rseq3, 13 )/loanAmt ), 2 ),
       rseq4, MMULT( fvarray * (loanAmt*(1-pp) + guess4*pp), ones ),
       prin, INDEX( rseq4, v+1, ) - INDEX( rseq4, v, ),
       i,  (TRANSPOSE(loanAmt*(1-pp) + guess4*pp) - prin) * --( v +1 > 1 ),
       cflows, TRANSPOSE(loanAmt*(1-pp) + guess4*pp),
       tap, IFERROR( INDEX( cflows, v+1, SEQUENCE( 1, 2 ) ), prin ),
       tapi, IFERROR( INDEX( tap, v+1, SEQUENCE( 1, 3 ) ), i ),
       tapic, IFERROR( INDEX( tapi, v+1, SEQUENCE( 1, 4 ) ), rseq4 ),
       tapic )

How it works I'm a bit pressed at the moment, so I cannot do a detailed explanation right now, so I will give a high level view:

The input variables are straight forward - as mentioned, the Dates are a helper column, so pmDates are the range of payment dates that were created by the last-weekday-of-month range mentioned above.

Array Shapers are just sequences of indexes and matrices that will be used in the shaping and calculation of the financials. They are used next to create a set of financial arrays for future value factors that will be used to create all of the financial outputs.

Payment Convergence is an iterative approach to creating the adjusted real payment amount (i.e. a replacement for PMT) that will truly balance the loan if paid according to the pmtDates that were input. This uses iterations that start with a guess and then adjusts the guess in each iteration. It converges faster then Newton-Raphson and is probably over-engineered for small loan/interest values, but can reliably scale to 7 figure loans and double digit interest rates. The final output is rseq4 which is the remaining Capital schedule that you asked for.

Prin (PPMT), i (IPMT), and cflows (payments) are calculated into columns in Results Columns. Result Table Construction then appends these by using nested over-indexing of the columns to form a single table called tapic (table annuities prin interest capital) which is the final result.

If it is valuable, I can expand on the methods and give more detail about the financials - just ask in comments, but gotta go for now.