moppenhe123
New Member
- Joined
- Jan 20, 2009
- Messages
- 1
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?
-
If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password.
Calculating Interest and Principal Payments in 365 day year
Andrew Fergus
MrExcel MVP
- Joined
- Sep 9, 2004
- Messages
- 5,462
- Office Version
- 365
- 2021
- 2016
- Platform
- Windows
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
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
Last edited:
Upvote
0
