Class/subject – Personal Finance / Maths or Business
Project/assignment: Spreadsheet analysis of typical consumer loan types and terms
Meet Kevin, an undergrad at a large university in Oregon. Kevin lives on campus and uses his laptop everywhere from the library to his dorm, to the hallway outside the classroom. And he never goes anywhere without his smart phone, which he checks before, during, and after class. Kevin’s university provides Live@EDU for all its students and staff. With these great tools, he can do his homework, stay on top of his social life, and have non-stop access to all of his files. And the fact that it’s free? Bonus.
This semester, Kevin’s course load will help illustrate each of the tools within Live@EDU, their features, and the many ways the Microsoft Office Web Apps make life easier for both students and faculty.
Spreadsheets and data analysis are often stigmatized as being boring, but a few simple tricks could show you how to compute information that can truly revolutionize your life—or at least your wallet. Excel puts exceptional abilities at your fingertips. As the industry-leading tool for data analysis, Excel can help you do everything from planning a budget to running sophisticated formulas.
Kevin’s class: Finance
Assignment: Payment analysis on common loans to determine amount paid in interest over the life of the loan.
Understanding the types, terms, and repayment information on common consumer loans are not only a cornerstone component of the finance curriculum, but a powerful life skill. The Excel Web App will be the tool used to run the calculations on the project. And by learning a few simple tricks, the students will become informed financial decision-makers.
Each student will be given a profile of a consumer and issued a set of terms for a home loan, a car loan, and student loan. Every profile is different, and after the students have run their calculations, they will collaborate together to view and analyse all the scenarios across the class.
To do this, some core functionalities of Excel will be used:
For the finance class loan comparison project, the class will be both calculating and comparing data. The multiple tabs at the bottom of the sheet organize your work into Worksheets. Kevin and his classmates use one sheet to calculate payment information, another to compare the data from the other profiles, and a third Worksheet to create a chart of the information they have calculated.
How to Format via different worksheets
Spreadsheets open in a Workbook, and there are individual sheets known as Worksheets. Using multiple pages or worksheets allows you to manage lots of information, yet break out data by different categories.
The finance professor walks Kevin and his class through the use of the PMT function on her shared Excel Class Workbook. Sharing documents allows you to grant permission to others to either access or even edit each individual file. The professor uses it in a class setting so that all students have immediate access to the up-to-date file.
By displaying the information in class and demonstrating how it works, the students can open and review her sample from the shared file when preparing their homework. The students will be able to calculate the information necessary by understanding the syntax or the way to write functions. Kevin and his class will calculate the payment on a home loan, a car loan, and a school loan.
Once the students have generated a payment for each of the home, car, and student loans, they are able to calculate how much they will be paying across the life of the loan by multiplying the payment by the number of payments. Once they subtract the principal, they are left with the amount of interest paid across the life of the loan. This calculation needs to be run on all the loans, and the best way to quickly do that is using Auto-fill. Auto-fill is a feature in Excel that lets you drag the formula over to other cells, automatically populating the content.
By defining the amount of interest paid across each of the home loans, the car loan, and the student loans, it’s time to calculate the grand total. They can do this by using the SUM formula, a helpful shortcut any Excel user will implement time and again.
Kevin’s class must determine the best option for each of the profile from all the scenarios in the class. By sharing each of their files with their fellow students, they are able to immediately access the other profile breakdowns. Filtering the results by profile name and total amount paid in interest will instantly show the most ideal loan term.
Excel also provides excellent options for graphs and charts. With the results of total amount paid in interest across each profile, Kevin and his class will use the data to create a graph. A bar graph is the correct option for the nature of the data they are creating, and by selecting the correct sets of data, Excel creates a bar graph automatically.
By working through this example, the students have learned how Excel can quickly sort, analyze, and graph data, as well as serve as a pivotal tool in making financial decisions.
All of this is possible with the Microsoft Excel Web App, but more powerful capabilities are available in the software version. The software version of Excel allows you to:
- Create PivotTables
- Publish complex data models to SharePoint
- Robust data manipulation possibilities
- Customizable Ribbons
To learn more, find helpful Excel resources here.
Want to share this with your students? Click here to find this information in an email template you can send to others, or share the link directly.