Calculating Interest and Principal Payments in 365 day year

[moppenhe123]

Does anyone know how to adjust the IPMT and PPMT formulas to account for a 365 day year versus a 360 day year? Or is there another way for this to be done?

 

[Andrew Fergus]

Hi and welcome to MrExcel!

These functions assume there are n periods per year and each period is the same duration. So there is no easy 'fix' when using these functions. Given you will be handling payment periods of differing durations, then my advice is to build this from scratch showing the monthly balance brought forward, repayment and interest charge for each month of the loan.

Please note that (IMO) you cannot use the PMT function because again this provides an incorrect result. If you follow the turorial per your yahoo question but use a goal seek to derive the amount of the payment instead of the PMT function. Also, use a daily interest calculation rather than balance x rate/12 per the tutorial suggestion, e.g. use balance x annual rate x (Date 2 - Date 1)/365.

Andrew