Thanks to a friend of mine, I decided to start adding pages – one for each of several projects. Most will be in Excel. The first one will be a loan repayment worksheet. This will allow you to see what happens to your balance when you change payment amounts or change WHEN you make your payment.
First thing we will need to do is figure out your monthly interest. Lenders calculate “monthly” as being 30 days, to keep it all uniform. There are 12 30-day periods a year. This worksheet will calculate a 12% interest rate. You can substitute your ACTUAL rate and change it whenever you have a new loan with a different interest rate. God save you if you have an Adjustable Rate Mortgage! (sorry)
Open Excel, we’re getting started! For the visual learners, follow this link to the video version on YouTube: Video version of Excel project: Loan Repayment – Day 1
Step 1 – Interest Rate calculations and Formatting your worksheet
Save this now and make sure your Excel window is as big as you can get it.
1. Go to the two columns as far to the right as you can still see without having to scroll. You should still see column A. Now type Interest Rates and hit enter. I started in Column P.
We’re going to create a little table to reference later. It’s titled “Interest Rates,” so we’ll merge and center that title.
2. Click and hold it down on the cell with “Interest Rates” in it, and drag your mouse over the cell to the right.
3. Click “Merge and Center” – Done!
4. Click on “Interest Rates”, hold CTRL, and hit B. Now it’s Bolded.
5. Now, find the Border icon on the menu bar (“Home” tab)
6. Click “Thick Box Border” – or just “Outside Borders.”
7. While still on the “Interest Rates” cell, hold “Shift” and tap your down arrow three times OR click and drag your mouse to highlight the next three rows in the two columns under Interest Rate.
8. Same as #6 – Click “Thick Box Border” – or just outside borders.
Yours should look like this (or similar):
9. Now, click the first cell under “Interest Rate”, and type “Yearly”, and hit enter.
10. In the cell under that, and type “Monthly”.
11. In the cell under that, and type “Daily”.
12. Next to Yearly (to the right), type .12 (or your interest rate expressed as a decimal. If it’s 7%, it would be .07)
13. Hit Enter
14. Click on the Percent Icon, on the Home tab.
NOW to some calculations! Woo hooo!
15. In the cell under the 12%, type =, then click on the 12% above. Excel puts the cell reference in your formula.
16. Hit /12 to divide yearly interest by 12 months. And hit Enter.
17. In the cell under that formula, type =, then click the cell above (that’s the one you just did the formula in) to reference that cell.
18. Hit /30 to divide monthly interest by 30 days. Hit Enter.
Done for today! We will be referencing this table later. Come back again for the next step!
Thanks for stopping by!