Right from the start of this question I have a complete dyslexia when it comes to accounting, so be gentle. I’ll use fictitious figures for this example
I wish to get a loan out and want to make repayments over a 3 year period, loan
![]() |
Patch reliability is unclear. Unless you have an immediate, pressing need to install a specific patch, don't do it. |
SIGN IN | Not a member? | REGISTER | PLUS MEMBERSHIP |
Home » Forums » AskWoody support » Productivity software by function » MS Excel and spreadsheet help » Monthly repayments (2K3)
The financial functions in Excel are correct, as far as I know, but a) there is some ambiguity as to how interest is calculated, and banks do not always keep strictly to the mathematical rules.
Keep in mind that if you pay monthly, the number of periods has to be in months and the interest rate too.
If you repay monthly at a yearly interest rate of 8%, one way of calculating the monthly interest rate is simply 8% / 12 = 0.666…%. But since interest is cumulative, that results in an effective interest of more than 8%. The effective interest can be calculated as follows: the monthly multiplication factor is 100%+8%/12 = 1.0066…. This leads to a yearly multiplication factor of 1.00666…^12 = 1.083. The increase is 0.083 or 8.3%.
You can use the same method to calculate the monthly interest that results in an effective interest of 8% yearly: the yearly factor is 1.08, thus the monthly one is 1.08^(1/12) = 1.006434. The increase is 0.006434 or 0.6434%.
Now it depends on which method the bank uses to determine the monthly interest rate…
You can also use RATE Function with the figures provided by the bank to calculate their “real” interest rate.
OK , I told you I never understand accountancy, a mental block
I calculated the monthly installments using PMT and then tried to see it as a reduction over 36 months. I want to do this so that I can put lump sums to pay i? off quicker. Why am I getting negative amounts towards the end/
Thanks Rob
I decided to grit my teeth and go through the PMT function ( I have a habit of over analysising like I do in Puzzles ).
I get irritated that I can make a calculation and it differs from the banks quote by about 50 pence per month in the banks favour, this is why I have difficulty in beliving this function is correct or if, as Hans says, they calculate it differently.
Donations from Plus members keep this site going. You can identify the people who support AskWoody by the Plus badge on their avatars.
AskWoody Plus members not only get access to all of the contents of this site -- including Susan Bradley's frequently updated Patch Watch listing -- they also receive weekly AskWoody Plus Newsletters (formerly Windows Secrets Newsletter) and AskWoody Plus Alerts, emails when there are important breaking developments.
Welcome to our unique respite from the madness.
It's easy to post questions about Windows 11, Windows 10, Win8.1, Win7, Surface, Office, or browse through our Forums. Post anonymously or register for greater privileges. Keep it civil, please: Decorous Lounge rules strictly enforced. Questions? Contact Customer Support.
Want to Advertise in the free newsletter? How about a gift subscription in honor of a birthday? Send an email to sb@askwoody.com to ask how.
Mastodon profile for DefConPatch
Mastodon profile for AskWoody
Home • About • FAQ • Posts & Privacy • Forums • My Account
Register • Free Newsletter • Plus Membership • Gift Certificates • MS-DEFCON Alerts
Copyright ©2004-2025 by AskWoody Tech LLC. All Rights Reserved.
Notifications