Today, lets learn how to make a simple timer app using Excel. First some background…,
Recently, I learned how to solve Rubik’s cube from my nephew. As a budding cuber, I wanted to track my progress. Initially I used the stopwatch in my iPhone. But it wont let me track previous times. So I thought, “Well, I can use Excel for this”.
So I made a small timer app using Excel. Its quite minimalistic. It has a single button. I press it and it tracks the start time (date & time stamp). If I press the button again, it records the duration.
This way, I can see my progress over next few weeks and may be plot the trend.
Demo of the Excel VBA timer
Here is a short demo. This is what we will be building.
Tutorial to make a timer in Excel
To make a timer app in Excel, first we need to understand the logic for this. If VBA apps can be defined on a scale of 1 to 10 (1 being easiest to develop and 10 being most complex), our timer app can be classified as 1.5. It is really simple. But nevertheless, it is a good idea to list down various ingredients and basic logic to follow.
So we need,
- A table to store the time stamps & durations
- A button (simple text box will do) to start & stop the timer
Set up the timer worksheet
In a blank worksheet, make space for a 2 column table. Type Time stamp & Duration as column headings and make a table from these (CTRL+T to insert the table)
Note: For the macro to work, you do not need a table. Any 2 column range will do. A table makes our timer app look sexy.
Also, insert a rounded rectangle and format it to look like a button (from Format Ribbon > Shape Styles, select something slick and pretty)
In a blank cell, type the word “Start”. Name this cell as timer.button.label
Now, click on the rounded rectangle button, go to formula bar and type =timer.button.label
💡 Tip: Yes, you can assign names or cell references to shapes. This way, whatever text is in the cell will be shown inside the shape.
Other names to make:
Although we can write VBA code without creating these names, our code will be readable with these names. So here we go:
- Select the header “Timestamp” of the table and name it as time.stamp.start
- Name the table as Durations from Table Design ribbon
- In a blank cell, write the formula =COUNTA(Durations[Timestamp])
- This counts how many timestamps are already inserted.
- Now name this cell as count.of.timestamps
We are done. Lets roll in to VBA.
Writing the VBA code for timer
Open VBE (Visual Basic Editor) and insert a new module in your timer workbook. There write this code.
Sub startStopTimer()
If Range("timer.button.label") = "Start" Then
Range("time.stamp.start").Offset(Range("count.of.timestamps") + 1).Value = Now
Range("timer.button.label") = "Stop"
Else
Range("time.stamp.start").Offset(Range("count.of.timestamps"), 1).Value = Now - Range("time.stamp.start").Offset(Range("count.of.timestamps"))
Range("timer.button.label") = "Start"
End If
End Sub
Assign this macro to the timer button
Right click on timer button and choose “Assign macro”. Select the startStopTimer sub from the list and click ok.
Now go ahead and test it. Assuming you have used same names as per this post, your timer should work.
How this macro works?
When you click on the timer button, you want one of the 2 things to happen.
- You want to start the timer
- You want to stop the timer
What you want to do can be checked with this logical check.
Range("timer.button.label") = "Start"
If this is true, then you want to start the timer.
Else, you want to stop the timer.
If you want to start the timer
Then, we need to go to the last row of the table + 1 and insert current time (now) in that cell.
This is done by,
Range("time.stamp.start").Offset(Range("count.of.timestamps") + 1).Value = Now
Once we do that, we need to change timer button’s text to “Stop”.
This is done by,
Range("timer.button.label") = "Stop"
If you want to stop the timer
Then, we need to go to the last row’s 2nd column of the table and print the difference between latest time (now) and starting time (last row, first column value)
This is done by,
Range("time.stamp.start").Offset(Range("count.of.timestamps"), 1).Value = Now - Range("time.stamp.start").Offset(Range("count.of.timestamps"))
Once we do that, we need to change the button text to “Start” by using this code:
Range("timer.button.label") = "Start"
That’s all. Our VBA code is rather simple.
One last step, formatting the duration
If you look at the duration, it could read something like 0.0042354. This is because duration is displayed as a fraction of day. So 0.0042354 means the duration is 0.42% of a day.
Now, wouldn’t it be better if we can show this in minutes and seconds?
To do that, select the entire table column of durations, press CTRL+1
Then, set formatting as custom and type code as [mm]:ss
And you are done!
Download Simple Timer Excel VBA workbook
Click here to download Simple Timer Excel VBA workbook. Play with it. Use it to track your Sudoku, crossword or knitting times. Or even Rubik’s cube times. See what trends and patterns you can uncover.
Do you use Excel for tracking time?
I know many companies use Excel based trackers to keep track of employee time. I personally use time tracking features of Excel for needs like this all the time.
What about you? Do you use Excel time functions like NOW, TODAY and VBA to track progress? What techniques you apply? Please share using comments.
Like tracking? You will love these
If you track things with Excel, you are going to find below tutorials very useful.
- Tracking issues & risks – Project management
- Tracking to dos – Project Management
- Expense tracker using Excel – 7 templates
- Annual goals tracker
- Bonus: Introduction to VBA – 5 part crash course
Note: Rubik’s cube image by Booyabazooka thru Wikimedia
20 Responses to “Mortgage Calculator with Extra Payments – Excel Download”
Kia ora Chandoo, thanks for this wonderful template brother. How can I modify this/ or do you have a similar template for a fortnightly
payment? Thank you
Morena Rayd. You can adjust the multiplier / divider to 26 (I think it was set to 12) to see fortnightly details. Watch the companion video to understand the steps / logic.
It is very simple and easy to use. Is user interface is attractive. It is very helpful and beneficial for calculations.
This is great, thanks a lot!
I think there is an error in the "You save X $ in interest". If i dont put any extra payment, it still says that I will save 19k in interest rate.
I too realized this problem. I will post an update once I fix the error.
Updated on 11-Sep: I fixed the problem with "interest saved" calculation. Turns out there was an internal bug in cumipmt function in Excel that reports incorrect values. Something new!
Please use the link above to re-download the correct file.
Hi Chandoo, do you have a template that will make changes to figures going forward if the interest rate changes part way through the loan? Thank you for your help.
I wanted calculator which can consider floating interest rate, can you please advise how to get that?
I love your tutorial. If I am making weekly acelerated mortgage payments, how to I account for that in the Eff term?
Thank yo so much,
Christine
Hi Chandoo. I want an excel template where I can have my favourite stocks listed for which the prices changes on a dynamic basis every day as per the market. I mean a sheet which is linked to NSE for price changes and other changes like daily low, Daily high, Yesterday closing, last 10 day range, 52 week high, 52 week low, one month high, one month low, one week high and one week low etc
The excel is awesome but I am getting #NAME? error with the field 'Old Interest Paid' and subsequently Original Interest and Money saved. Can you tell me how to fix that. I am trying to check if I should complete my loan by the extra payments or instead invest the same amount elsewhere
#NAME error could mean your Excel doesn't have one of the functions I am using. Can you tell me what version you are using? If I am not mistaken, this file requires Excel 365.
Hi Chandoo, I am also getting the same error. I am using Office 2019. Would you be able to recommend a fix for this?
Regards,
How do you change the dollar to rupees for whole sheet to match indian currency
You can select the values and apply rupee formatting from cell formatting options.
Hey Chandoo,
Thank you for making the excel spreadsheet! It is great, but there another, little known money saving action that you can take to DRAMATICALLY reduce your total interest owed on your mortgage allowing you to pay it off 40% to 60% faster. I am going to share with you for free. It's called recasting your mortgage, which is a form of re-amortizing the interest that you owe on your loan by paying the the bank an early payment of $10,000 or more. The bank will modify your loan to cancel the original interest that you owe, and recalculate the total interest based on the new lower loan loan amount for the remaining term of the loan, instead of the original loan amount. This pays off the most expensive section of interest on your loan which is the next owed interest, instead of cancelling the interest at the end of your loan. Your charts show the interest being canceled on the last payments, however the interest can be canceled on your next upcoming portion of the loan, but keeps the ending date the same. However, the result of recasting the loan is you, pay the loan off 2 or 3 times faster, by paying off the portion of the loan with the highest interest cost. I am wondering if you can build a excel spreadsheet that shows this for me? I will pay you for making it. I am a mortgage loan officer and would like to use it to help my clients see the impact of paying off their loans this way. Thank you for considering this!
HI Chandoo.
Excellent sheet i watched your video and tried to edit sheet to suit my current mortgage however i cant get it to work. I am trying to set it to weekly payments.
I edited the monthly payments to =PMT(E7/52,E6*52,E5)
eff.term =ROUND(NPER($E$7/52,$E$10,$D13),0)
principal term to =PPMT($E$7/52,1,E13,D13)
cb w/o to =IF(C13=1,$E$5,J12)+PPMT($E$7/52,C13,$E$6*52,$E$5)
and interest paid to =IPMT($E$7/52,1,E13,D13) but i cant edit the arrays to increase the range to 1560 from 360, so i just added the numbers in and that worked for the array on the left but the old interest paid array im stuck. And the graph well the graph is useless now and i have no idea how to fix that. no idea at all.
Any help would be great.
Cheers
To change the NUMBER OF ROWS, go to cell C13 and change the formula to
=SEQUENCE(E6*52)
The chart won't auto-adjust. So you need to adjust the chart's source data range to capture all rows.
Hey man, I really appreciate your contribution, this template is awesome, I am sure it will help me to plane my financial life, and also to pay debts earlier and create wealth.
Thank you!!!
how do i edit this to use months instead of years for term? i have a 90 month loan (7.5 years) that i am trying to use with this and it forces me to use 7 or 8 in years.