User level: Level 1 (13 points)Is there a formula to show monthly payments? I know the loan principal sum and the calendar start and end date.
Posted on Sep 18, 2026 6:56 AM
User level: Level 9 (62,211 points)Sep 18, 2026 10:53 AM in response to paulkcorcoran
Ultimately, the function you want is PMT() which calculates the periodic payment on a loan at a fixed rate. There are a few pieces missing though...
First, you don't mention interest - it's fine if this is an interest-free loan, but you didn't specify that.
Second, PMT() requires a 'number of payments' parameter, so you'll need to calculate the number of months between the start and end dates.
Here's a quick example I threw together:
The formula in cell B5 is:
=PMT(0,DATEDIF(B3,B4,"M"),B2,0,0)
The first parameter (0) indicates the interest rate per period. If it's an interest-free calculation, set it to 0, otherwise enter the APR/12 (since you're looking at the monthly rate)
The second parameter uses DATEDIF() to calculate the number of months between the start and end dates in cells B3 and B4, respectively.
User level: Level 9 (62,211 points)Sep 18, 2026 12:29 PM in response to paulkcorcoran
Glad I could help.
Of course, if there's no interest, you could just divide the principal by the number of months (via DATEDIF to get the number of months) and get the same result:
=B2÷DATEDIF(B3,B4,"M")
(± values notwithstanding -- PMT will report a negative number as an outgoing payment reducing the outstanding loan amount, whereas principal/number of months returns a positive value)
User level: Level 1 (13 points)Sep 18, 2026 11:52 AM in response to Camelot
Thanks. That works. I didn't have any interest to consider as it was interest only on this occasion. But the formula will be useful in the future.
Originally posted at discussions.apple.com/thread/256359740