PMT with different compounding and payment periods

[jamesblack90]

Hi guys,

Having the following issue where I want to be able to calculate the monthly/fortnightly/weekly repayments where interest is calculated daily but compounded monthly. I am using the following so far:

=ABS(PMT(B21/12,IF(B23="Weekly",B19*52,IF(B23="Fortnightly",B19*26,IF(B23="Monthly",B19*12,""))),B17,0))

Where:
B21 = %pa
B19 = # years

The Monthly payment calculates perfectly, but the problem is if I change B23 to Weekly or Fortnightly, the repayments actually get higher, and I need interest to continue to compound monthly, but take into account the reduction of principal at weekly or fortnightly intervals... Any ideas?

 

[jamesblack90]

Don't worry. Worked it out.

=ABS(PMT(B26/12,B22*12,B20,0))*IF(B28="Weekly",12/52,IF(B28="Fortnightly",12/26,IF(B28="Monthly",1,1)))