I know that doctors, in general, don’t like working with numbers. However, I do know that doctors like to use calculators when necessary in the clinic. I have an app on my iPhone that does radiation dose conversions when I need it in the clinic.
In personal finance, there are a couple of key spreadsheet functions you should be familiar with. In a previous post, I discussed the Future Value function. In this post, I’ll review the Payment (PMT) function, which is a great function for student loan and retirement calculations.
Calculations using the payment (PMT) function with Google Sheets
You can build complicated spreadsheets or use fancy software to more precisely do these types of calculations, but the simple PMT function can get you a ballpark answer.
To use the payment function, simply type =PMT( into any cell of the spreadsheet
Once you type in =PMT(, Google Sheets knows you are trying to calculate a payment function and guides you right along each step of the way:
Let’s run through the variables in the payment function, one-by-one:
Rate
This is the interest rate (either that you will pay, or you will receive if you are investing). Always use a positive value for this.
Number of Periods
This is the number of periods in the payment calculation. For most calculations, it’s the number of payments or contributions you make. You can input annual payments or monthly payments, but remember to adjust the interest rate accordingly (i.e. use a monthly interest rate if you are using monthly payments).
Present Value
This is the starting value. Use positive numbers for loans (e.g. student loans, mortgage), and negative numbers for investments (e.g. retirement calculations)
Future Value (optional)
If you leave this number blank, then it is assumed to be zero. This is good for student loan and mortgage payoff calculations, but for retirement calculations, you will want to put in a (negative) number for this.
End or Beginning (optional)
This optional variable determines whether the payment is due at the beginning or the end of a period. Use 0 for the end of the period and 1 for the beginning of the period.
Microsoft Excel Payment (PMT) Function
Microsoft offers a limited version of Microsoft Excel for free online, which includes the payment function.
To use the payment (PMT) function in Excel, simply type =PMT( into any cell of the spreadsheet:
The order of the PMT function variables is the same in Microsoft Excel as in Google Sheets.
Example Problems
Student Loan Payback
Dr. A finishes residency with $250,000 in debt at an interest rate of 5% annually. She would like to pay off her student loans in 5 years (60 months) after residency. She wants to make monthly payments at the end of each month to pay down her student loans. What should be her monthly payments to pay off her loans?
- Rate: 5%/12 (while not strictly correct if her loan compounds annually instead of monthly, this works for this back of the envelope calculation).
- Number of Periods: 60 (months)
- Present Value: 250,000 (remember that this number is positive for loans)
- Future Value: leave blank or input 0 (we want the loans to be paid off at the end of five years)
- End or beginning: 0 (zero for end, one for beginning)
= =PMT(5%/12, 60, 250000, 0, 0)
= -$4,717.81
Her monthly payment should be $4,717,81 per month to pay off your $250,000 5% student loans in 5 years.
Saving For College
Dr. B has a newborn and is trying to save for her future private college. He would like to pay for his daughter to go to a private school in full. He estimates that college will cost $125,000 a year in 18 years, so he will need to save $500,000. Assuming an investment return of 8%, how much will he need to save at the beginning of each year for college?
- Rate: 8%
- Number of Periods: 18 (years)
- Present Value: 0
- Future Value: -500,000 (remember to use negative numbers for investments and savings)
- End or beginning: 1 (zero for end, one for beginning)
=PMT(8%, 18, 0,-500000,1)
= $12,362.08
Dr. B will need to save $12,362.08 per year from his daughter’s birth to accumulate $500,000 for her future private school college education.
Investing For Retirement
Dr. C finishes residency with $200,000 in debt. She wants to have $5,000,000 in her retirement account in 35 years. Assuming an investment return of 4%, how much will she need to save at the beginning of each year to meet her retirement goals?
- Rate: 4%
- Number of Periods: 35 (years)
- Present Value: 200,000 (remember that this number is positive for loans)
- Future Value: -5,000,000 (remember to use negative numbers for investments and savings)
- End or beginning: 1 (zero for end, one for beginning)
=PMT(4%, 35, 200000,-5000000,1)
=$54,972.26
In order to have $5,000,000 in her retirement account in 35 years (assuming a 4% return), she will need to save $54,972.26 each year.
Example Calculator
Maybe you just don’t want to have to type in the variables yourself in an explicit spreadsheet function. I went ahead and made a simple spreadsheet that will run the payment function for you. Bookmark this page for future reference whenever you want to run some numbers.
Conclusion
The payment function is a way to quickly do compound interest calculations using a basic spreadsheet editor. It’s as easy a typing a single function in a spreadsheet like Microsoft Excel or Google Sheets. If you want to just type in numbers, feel free to use the spreadsheet I’ve made above to run the numbers.
What do you think? Do you use the payment function to do compound interest calculations?
My name is Tom Peacock from USA, I want to say thank you to Dr Emu for the good thing he has done for me, Though am not sure if this is the best forum to show my joy and happiness for what he has done for me but i can’t hide my happiness and my joy so i have to share it with people, my marriage got crashed about two years ago and i tried all i could within my power but to no avail. I saw a post and testimonial about the good things Dr Emu has been doing so I decided to give it a try. though he is always a busy man but when he responded back to my email, he gave me 48 hours for my marriage to be restored really just like he said my marriage was restored since then I am happy and i am living happily i am so grateful to Dr Emu you can always email him here: {emutemple@gmail.com} or WhatsApp: {+2347012841542}
BITCOIN FRAUD /SCAM TRACE & RECOVERY EXPERT
Have you lost hope of ever recovering your money from scam brokers? I have good news for you and yes it is 97% possible for you to recover
your money through binary cash refund (BCRF)but to do this you need an expert ,I lost over 76,000$ to fake broker and I lost any hope of
ever making profit through binary trading until I met an awesome recovery Hacker known as RecoveryMasters who introduced me to (BCRF)..and to recover any lost money to any Cryptocurrency i was able to recover my money and with an amazing recovery Hacker guidance I have been able to make profits after recovering my lost funds. l recommend RecoveryMasters to anyone ( Recoverymasters@email.cz ) or WhatsApp him on +15-5120-2-23-35
Don’t forget to mention Claudie recommended you
“Michealmurphy At Repairman DOT Com” remains the best in Bitcoin, wallet, password and funds recovery. I had a cold storage wallet that I bought some years back which I use in storing my crypto. When I went back to withdraw my coin I realized my wallet had been compromised and my coin was gone. After a week of not having access to my wallet, I started researching, I found Michael Murphy and they had the best reviews. They tracked the coin using the hacker’s transaction signature print in an hour and only took their 20% fee which I was more than happy for them to have. Highly recommend their service.
Are you worried about your stolen BTC or other cryptocurrencies? Contact TheHackerspro, a leading cryptocurrency recovery service, and rest easy knowing that your stolen coins are in good hands.
The team at TheHackerspro is made up of experts in blockchain technology, cryptography, and forensic analysis. They use specialized software and tools to trace stolen coins and identify the wallets to which they have been transferred. They are also able to work with law enforcement agencies to track down the scammers and recover stolen coins.
In one recent case, TheHackerspro was able to recover over 200 BTC from outsourced leveraged wallets. Their professionalism and speed in recovering the stolen coins were remarkable. So if you have fallen victim to investment scams, pig buttering scams, or Metamask phishing links, don’t hesitate to contact TheHackerspro at support@thehackerspro.com. They have the expertise and tools needed to help you recover your stolen coins and get back on track.
Recording success in Cryptocurrency, Bitcoin is not just buying and holding till when bitcoin sky-rocks, this has been longed abolished by intelligent traders ,mostly now that bitcoin bull is still controlling the market after successfully defended the $20,000 support level once again and this is likely to trigger a possible move towards $40,000 resistance area However , it’s is best advice you find a working strategy by hub/daily signals that works well in other to accumulate and grow a very strong portfolio ahead. I have been trading with Mr Bernie doran daily signals and strategy, on his platform, and his guidance makes trading less stressful and more profit despite the recent fluctuations. I was able to easily increase my portfolio in just 3weeks of trading with his daily signals, growing my 0.9 BTC to 2.9BTC. Mr Bernie’s daily signals are very accurate and yields a great positive return on investment. I really enjoy trading with him and I’m still trading with him, He is available to give assistance to anyone who love crypto trading and beginners in bitcoin investment , I would suggest you contact him on WhatsApp : + 1424(285)-0682 , Gmail : (Bernie.doranfx01) or Telegram : bernie_fx for inquiries , Bitcoin is taking over the world
TRACE AND RECOVER YOUR LOST CRYPTO THROUGH ULTIMATE HACKER JERRY.
Learn more;Web http://www.ultimateshackjerry.com
Last year I stumbled across a cryptocurrency platform Advertisement online and I felt compelled to watch them since I had little knowledge of how profitable cryptocurrency is. I was immediately intrigued by it and decided to invest with the investment firm., on my first trial, I deposited $113,000 to the platform.My profit had accumulated so quickly after 48hrs that I became more interested and decided to add $215,100 to my initial investment.on attaining my profit target I requested for withdrawals. This company then began asking for more funds to activate my withdrawals.This made me suspicious, so I decided to consult a Crypto Expert. I came across Ultimate Hacker Jerry who advised me that I had been scammed but was also an Expert in Crypto Recovery Services. This expert Ultimate Hacker Jerry was able to recover all my Crypto a total of $328,100.I must recommend this erpert to any Scam victim that has been defrauded and have your Crypto recovered back by Ultimate Hacker Jerry.
CONTACT;Mail Ultimatehackerjerry@seznam. cz \
Whatsapp +1(520)282-7151.
HOW I FINALLY RECOVERED MY LOST CRYPTO: I lost all my crypto to a fake investment scam to someone I met online. I started searching for help legally to recover my funds, and I came across a lot of Testimonies about HACKER STEVE. I contacted him, providing the necessary information and it took him and his team of experts about 36 hours to locate and help recover my stolen funds. I am so relieved and the best part was, the scammer was located and arrested by local authorities in his region. I hope this helps as many out there who are victims. I strongly recommend Steve professional services for assistance with swift and efficient recovery (Crypto, Credit card, Forex, NFT, etc) on Gmail: Hackersteve911@gmail.com | https://hackersteve.great-site.net/