Excel offers a vast number of functions, and one category that usually requires me to double-check my notes is financial functions—things like calculating loan repayments, future values, and similar concepts. Luckily, we now have a fantastic assistant in Copilot to help refresh our memory on how these work.
In this blog post, we’ll dive into some examples. You can follow along using the exercise file provided below:
Let’s begin by calculating a loan repayment. I’ll request Copilot to determine the monthly payment for a five-year, $50,000 loan with a 5% interest rate. I’ll enter that directly into the prompt box, and Copilot will convert it into an Excel formula that we can copy into the worksheet to find the solution.
This is quite impressive, but what if we want to modify these figures? Hardcoding values in Excel is typically not recommended, even for basic calculations. And in fact, those of you with eagle eyes may have noticed that I forgot to even specify in the prompt that it’s a five-year period—that’s just what Copilot luckily assumed I wanted.
Instead, we can explicitly define our input numbers in the workbook, then direct Copilot to those cells and construct the formula accordingly. This approach lets us easily adjust the values, reuse the formula, gain a clearer insight into the data, and ensure we’ve specified all the inputs—not just whatever the AI felt like giving us.
Calculate the payment for the loan amount in cell B1 at the annual interest rate in cell B2 for the number of years in cell B3 and the number of payments per year in B4.
We’ll employ the same strategy of inputting the values directly into the worksheet and referencing them by their cell locations in the subsequent examples, each of which will be located in its own worksheet within the demo download file.
Now, let’s explore calculating the present value. This represents the current worth of a future sum of money, adjusted to today’s value using a specified interest rate. I’ll prompt Copilot:
Find the present value of receiving a monthly payment in B1 for the term in B2 years at the annual rate in B3, with payments at the end of each month.
Great work! Let’s estimate the lump sum in the opposite direction now. The future value is the amount a current sum of money or investment will grow to at a specified time in the future, based on an assumed interest rate and compounding period. I’ll ask Copilot:
Compute the future value of monthly savings of B1 for the term in B2 years at the annual rate in B3, with payments at the end of each month.
This is pretty neat. As we go along, Copilot provides us with basic pointers and explanations about how these calculations function. We could even keep asking Copilot questions if something isn’t clear.
Personally, though, I find numbers make the most sense through hands-on trial and error. I enjoy tweaking my inputs and observing how the outputs shift in real time. In financial modeling, this approach is often called sensitivity analysis. We can use one-way and two-way data tables to explore how outputs respond when one or two inputs are adjusted. Let’s see if Copilot can help set this up for us. To keep it simple, I’ll just provide the inputs as if Copilot will hardcode them:
Create a summary table showing the future value assuming a 5% annual growth rate of $200 monthly payments between 3 and 20 years.
And, what do you know? Copilot created this one-way data table for us, placing into a new worksheet:
Some of our inputs here are hardcoded, so we could either manually tweak them to make things more dynamic or set it up with Copilot’s assistance. Copilot also gives us a sneak peek of the results—if we want to see the full 20-year span, we can just drag down the formulas it generated.
We could take it up a notch and ask Copilot for a two-way data table, one that tracks changes in inputs across both columns and rows. This time, I’ll tweak the annual rate AND the duration:
Create a summary table showing the future value assuming between a 3-7% annual growth rate of $200 monthly payments between 3 and 20 years.
And we get another table back:
Nice job! Let’s wrap up with a couple more use cases. Keep in mind that most of these rely on their own specific Excel formulas. We don’t even need to know their exact names, though they’re usually pretty intuitive and tied to the use case—learning the function’s mechanics alongside its real-world application can mutually reinforce our understanding.
Anyway, let’s reverse-engineer the interest rate we’d need to pay off a loan over a set period, given our loan amount and payments:
Calculate the annual interest rate needed to pay off a loan in B1 over B2 * B3 periods with payments of B4. Show the annual rate.
And that’s where the fittingly named RATE() function comes in. Lastly, let’s figure out how many periods it’d take to pay off a loan based on a few inputs:
Calculate how many months it takes to pay off a loan in B1 with monthly payments of B2 at an annual rate of B3.
Keep in mind that Excel offers even more financial functions worth exploring—you can check them out in the documentation (or even ask Copilot about them). Many are super practical and simple to set up as two-way data tables, just like we did with one of these examples.
Excel’s financial functions deliver a surprisingly robust yet approachable toolkit—after all, Albert Einstein allegedly dubbed compound interest the 8th wonder of the world. For those of us who dip into these tools now and then and might forget the specifics, I hope this serves as a handy refresher.
What questions do you have about Excel’s financial functions or using Copilot with Excel more generally? Let me know in the comments.
Leave a Reply