PMT function with different desired period

[kornsatu]

Hello,

I have a question about the PMT function applied on different period (annual, semi-annual, quarterly and monthly).
As per the screenshot, I use the IPMT, PPMT and PMT to calculate interest, principal and debt service respectively.

However, I am curious about my calculation if it is correct because I got the different interest and debt service amount differently between each considered period.
If this calculation is correct, this means that if we consider monthly calculation will have lowest debt service amount.

Kindly suggest.

Thanks a lot

 

[StephenCrump]

Your calculations look correct to me ...

	A	B	C	D
1		Monthly	Semi-annual
2	Loan	198,546	198,546
3	Nominal interest rate	5.5%	5.5%	p.a.
4	Years	15	15
5	Frequency	12	2	payments p.a.
6	PMT	$1,622.29	$9,805.08
7	Total interest	$93,465.57	$95,606.37
Sheet1

Cell Formulas
Range	Formula
B6:C6	B6	=PMT(B3/B5,B4*B5,-B2)
B7:C7	B7	=B4*B5*B6-B2

The higher the repayment frequency, the lower the total interest. This is because principal is being paid off slightly earlier, on average, hence less interest is accruing.

The PMT calculation is based on the assumptions that:
- The interest rate for the repayment period = nominal interest rate / frequency
- Interest compounds at the repayment frequency
- Repayment periods are of equal length.

In practice, repayments, and hence total interest paid, may be calculated slightly differently.