Most know how to use a spreadsheet to do basic operations like add, subtract, multiply, and divide. We know some basic functions like =SUM and =AVERAGE. These simple operations are good for lots of useful spreadsheets. We can keep track of our checking account, track our gas mileage, keep a list of birthdates, maintain a database of names and addresses and email addresses, etc.
It is that time of year once again. The date may have been moved to July 15th but the taxes still have to be paid. And for those who have investments and are over 70 and a half years old, there could be a minimum amount you are required to withdraw from those investments to pay an income tax on.
We can use a spreadsheet to make that calculation by just inputting the age and the amount of investment and the factor for our age. The factor table to use can be found online. Just google “RMD table 2020”.
Vlookup
Using the VLOOKUP function we can create a spreadsheet with the table built-in so each year we simply enter the amount of our investment and it computes the RMD (Required Minimum Distribution).
The first parameter is the location of the lookup value. In this case, it is the age of the person. We could key this is each time. I used the DATEDIF function to calculate it from the date of birth. In this way, each year I only have to key in the current investment amount to find out the RMD.
That formula is =DATEDIF(B5,NOW(),”Y”) and it uses the birthdate (DOB) and today’s date to come up with the current age.
Note: If your birthdate is between January 1 and the date you are running this calculation, adjust the formula to subtract a year since it is the age you were at the end of the tax year that needs to be used. Change it to =DATEDIF(B5,NOW(),”Y”)-1
The correct factor is determined using =VLOOKUP(B6,A12:B56,2)
B6 is the cell containing the age. The Table starts in A12 and ends in B56.
Note: I only show the first seven ages in the screenshot.
The ”2″ indicates that the result will be found in the second column of the table.
Although I used this to demonstrate RMD calculations, VLOOKUP has many other uses. When teaching, I would use it to calculate letter grades for students based on their scores. I am sure you can think of many other uses as well.
This function is in Excel and Google Sheets and most other spreadsheet programs. Enjoy another tool from my toolbox.
Dick
—
