Question

What are some ways that you can apply at least three of the advanced functions in excel that you learnedl in excel class. How would you use them at home, work or school? Please be specific. 250 word min

Answer 1

A lot of people view Excel as a tool that’s only good for business applications, but when you see the simple and useful formulas below, you’ll see that Excel can also help you solve real life problems at home.
Whether you’re shopping for a new car loan, want to figure out which mutual fund investment is best for you, or if you’re just trying to make sense out of your bank account, Excel is a powerful tool that can help.
The 15 formulas below were carefully selected for both simplicity and for how powerful they are with helping regular people figure out complex issues. If you’re a teacher trying to sort out student grades, a parent trying to get a handle on your household spending, or a young couple shopping for your first home loan, the formulas below can help.
Financial Formulas
Shopping for a new home and confused by all the mortgage lingo? Looking for a new car and getting confused by the car loan terms the sales person keeps throwing at you?


Have no fear. Before you start shopping, do your research with Excel by your side!
PMT – Payment
Whenever you’re comparing any loan terms and want to quickly figure out your actual monthly payment given different variation in terms, take advantage of the powerful (and simple) PMT formula.


Here’s what you need to use this formula:
the percentage interest rate of the loan
the term of the loan (how many payments?)
the starting principle of the loan
future value, if for some reason the loan will be considered paid off before it reaches zero (optional)
type of loan – 0 if payments due at the end of each month, or 1 if they’re due at the beginning (optional)
Here’s a cool way to quickly compare a variety of loans to see what your payments will look like; just create an Excel sheet that lists every potential loan and all available information about them. Then, create a “Payments” column and use the PMT formula.


Just grab the lower right corner of the PMT cell you just created, and drag it down so it calculates the payment total for all of the loan terms listed in the sheet.
Personal Loans Online -- How Peer-to-Peer Lending Is Changing the Game
Personal Loans Online -- How Peer-to-Peer Lending Is Changing the Game
A number of services on the market right now allow lenders to provide credit, and for borrowers to gain access to funds without dealing with a bank. They're shaking up the finance game.
READ MORE


As you can see, when you can compare loan terms side-by-side, some stark realities show forth. For example, for a car loan of $19,500, if you take the time to show around and get a loan that’s just a few interest points lower, it could save you a lot of money each month, and thousands of dollars over the life of the loan.
(A very big thank you to Mark Jones (@redtexture on Twitter) who pointed out that for PMT and FV formulas, you’ve got to be very careful about using the same period – in this case using monthly payments requires dividing the interest term by 12 months)

This is why our readers are so great – thanks for helping with this fix Mark!
FV – Future Value
The next formula comes in handy when you are looking to invest some money into something like a CD (Certificate of Deposit), and you want to know what the CD will be worth at the end of the term.
Here’s what you need to know to use the FV formula:
interest rate of the loan
number of payments (or investment term in months)
the payment for each period (usually monthly)
current starting balance (optional)
type of loan – 0 if payments due at the end of each month, or 1 if they’re due at the beginning (optional)
So let’s compare several bank CDs using the terms that you know from the information the banks have given you. In the example below, let’s say you have a $20,000 inheritance to invest in a CD.
As you can see, interest rates are again represented in decimal format (take the interest rate the bank gave you and divide by 100), and payments are zero, because CD’s are typically based on a starting value and a future value paid out. Here’s what the comparison looks like when you use the FV formula for every CD you’re considering.


Without a doubt, the higher interest CD over a longer period of time pays out handsomely more. The only drawback of course is that you can’t touch any of your money for three whole years. For a lot of people, that’s just a little too long, but that’s the nature of investing!
Logical Functions (IF or AND)
Most banks these days give you the ability to download nearly a year’s worth of bank transactions to a format like CSV. This is a perfect format to analyze your spending using Excel, but sometimes the data you receive from banks is very disorganized.
Using logical functions is a great way to spot overspending.
Ideally, the bank either automatically categorizes your spending, or you’ve set up your account so that things are placed into spending categories. For example, any restaurants we go to get labeled with the “DiningOut” label.


This makes it easy to use a logical function to identify whenever we’ve gone out to eat and spent over $20.
To do this, just create a logical function in a new column, looking for any value where the category column is “DiningOut” and the transaction column is larger than -$20 (the comparison below shows “<“, less than, because the values in column C are all negative. Here’s what that looks like:


Using IF and AND together in one formula looks tricky, but if you think about it, it makes perfect sense. The IF statement will output the dollar amount (C2) if the AND statement is true, or FALSE if it isn’t. The AND statement checks whether the category is “DiningOut” and the transaction is greater than $20.


There you have it! Without having to manually sift through all of those transactions, you now know exactly those times when you’ve overspent in a certain category. As usual, Excel formulas turn out to be a massive time-saver.