Published By
Created On
10 Jan 2022 14:39:01 UTC
Transaction ID
Cost
Safe for Work
Free
Yes
Learn 5 Financial Functions in Excel | PMT, RATE, NPER, PV, FV
Learn more:- https://www.exceltutorial.net/
============================================================
Financial Functions Playlist:- https://youtube.com/playlist?list=PLhvn6aQRdimU1umiFNnQrVugsq_TJ6LDD
============================================================
We all know that financial calculations are very important for financial statements, budgets and financial planning. We also know that these calculations can be done with a calculator or spreadsheet.
But there are five key financial functions in Excel namely PMT, RATE, NPER PV FV which are commonly used to perform these calculations.
In this video, we will learn how to use these functions on Microsoft Excel 2021 so you can see the power of using spreadsheets as opposed to calculators!
First Letsee the syntax of PMT function;
Syntax: PMT(rate, nper, pv[, fv][, type])
rate: Is the interest rate per period. We need to use this argument as a real number
.nper: The number of payment periods. We need to use this argument as an integer value in financial functions.
pv: This is the present value or loan amount that we are trying to calculate the monthly payments for, again it needs to be a real number. There can't be any spaces before or after the comma in this argument.
fv: This is the future value of the loan or present value, it can be omitted if you don't want to calculate it.
type: The type of payment schedule either 0 for normal or -l for late payments. We will use 0 in our examples.
In this function first argument is the rate of interest and second argument is number of periods. In third parameter we have to enter present value or loan amount which will be used as a factor for calculation. Fourth optional financial value can be optionally entered and financial value here refers to the cash flow. Finally we have type of payment which can be 0 (due at beginning).
Now Let's look at the syntax of RATE Function;
Syntax: RATE(nper, pmt, pv, fv, type)
nper: The number of payment periods in an annuity. We need to use this argument as an integer value
pmt: Payment made each period on a loan, investment or savings account. It is often expressed as a percentage of the principal which is used here for financial calculations. Our financial function requires it be entered as a real number with no spaces before or after the comma.
pv: Present value is the total amount that a series of future payments is worth right now, financial function requires it be entered as a real number with no spaces within this argument.
fv: This element stands for financial value and can either be zero or omitted since we don't always need to calculate it.
type: The type of payment schedule either 0 for normal or -l for late payments, financial function requires that we use 0 as its argument.
rate()
...
https://www.youtube.com/watch?v=H7CTK7uR92g
============================================================
Financial Functions Playlist:- https://youtube.com/playlist?list=PLhvn6aQRdimU1umiFNnQrVugsq_TJ6LDD
============================================================
We all know that financial calculations are very important for financial statements, budgets and financial planning. We also know that these calculations can be done with a calculator or spreadsheet.
But there are five key financial functions in Excel namely PMT, RATE, NPER PV FV which are commonly used to perform these calculations.
In this video, we will learn how to use these functions on Microsoft Excel 2021 so you can see the power of using spreadsheets as opposed to calculators!
First Letsee the syntax of PMT function;
Syntax: PMT(rate, nper, pv[, fv][, type])
rate: Is the interest rate per period. We need to use this argument as a real number
.nper: The number of payment periods. We need to use this argument as an integer value in financial functions.
pv: This is the present value or loan amount that we are trying to calculate the monthly payments for, again it needs to be a real number. There can't be any spaces before or after the comma in this argument.
fv: This is the future value of the loan or present value, it can be omitted if you don't want to calculate it.
type: The type of payment schedule either 0 for normal or -l for late payments. We will use 0 in our examples.
In this function first argument is the rate of interest and second argument is number of periods. In third parameter we have to enter present value or loan amount which will be used as a factor for calculation. Fourth optional financial value can be optionally entered and financial value here refers to the cash flow. Finally we have type of payment which can be 0 (due at beginning).
Now Let's look at the syntax of RATE Function;
Syntax: RATE(nper, pmt, pv, fv, type)
nper: The number of payment periods in an annuity. We need to use this argument as an integer value
pmt: Payment made each period on a loan, investment or savings account. It is often expressed as a percentage of the principal which is used here for financial calculations. Our financial function requires it be entered as a real number with no spaces before or after the comma.
pv: Present value is the total amount that a series of future payments is worth right now, financial function requires it be entered as a real number with no spaces within this argument.
fv: This element stands for financial value and can either be zero or omitted since we don't always need to calculate it.
type: The type of payment schedule either 0 for normal or -l for late payments, financial function requires that we use 0 as its argument.
rate()
...
https://www.youtube.com/watch?v=H7CTK7uR92g
Author
Content Type
Unspecified
video/mp4
Language
English
More from the publisher
Controlling
VIDEO
HOW T
how-to-use-xlookup-function-in-excel
Learn more:- https://www.exceltutorial.net/
============================================================
Lookup Playlist:- https://youtube.com/playlist?list=PLhvn6aQRdimXjKB1EOoRWznYZtqRe7B2z
============================================================
If you have Excel 365 or Excel 2021, be sure to use the XLOOKUP function instead of VLOOKUP. The XLOOKUP function is easier to use and has some additional advantages.
For one thing, it can return a value from a list that may not be in alphabetical order. This makes it great for looking up data on a column with a lot of repeating values like customer names or product types.
In addition, if your lookup list contains duplicates, xlookup will only pick the first matching item from the list while vlookup returns all matches which could lead to errors.
Lets Look at Syntax Of XLOOKUP function;
Please note: This example is for Excel 2021 and earlier.
XLOOKUP(lookup_value,table_array,col_index_num,[range_lookup])
Lookup value-This argument should contain the sought value. In other words it's a known input to be matched in your table array which can be cell reference or a list of values.
Table array-This argument is the range (your table) that contains your data set and you want to retrieve from it. Col_index_num -You may know this as the column index number, specifies which columnto return value from if multiple matches are found. If omitted then first column is assumed.
Range lookup-This optional argument controls how xlookup behaves if it finds a match in the table array. If TRUE (default), xlookup will return the largest value that is less than or equal to lookup_value. If FALSE, xlookup will return the first exact match it finds.
Table of Content;
00:00 Introduction
00:49 Xlookup Basics
03:34 Left Lookup With XLOOKUP
05:06 Horizontal Lookup (HLOOKUP) With XLOOKUP
06:19 NOT FOUND Error With XLOOKUP
07:55 Multiple Values Using XLOOKUP
09:40 Approximate Match With XLOOKUP
13:17 Last Match With XLOOKUP
16:00 Conclusion
Other Playlists:-
Cell References in Excel - https://youtube.com/playlist?list=PLhvn6aQRdimVC65nzr2BebSN9kPSdK6qH
Logical Functions in Excel :- https://youtube.com/playlist?list=PLhvn6aQRdimWBCKHNrLEqubXDMaHeINU1
Count and Sum Functions in Excel - https://youtube.com/playlist?list=PLhvn6aQRdimVHQA5zqrJ6XG6e5jP5hyQN
Learn Data Validation In Hindi: https://youtube.com/playlist?list=PLhvn6aQRdimXirM12gw1eijuiMYta-Oqf
Learn Excel Tutorials Online:- https://www.youtube.com/playlist?list=PLhvn6aQRdimWKL2jOmJxQBEBTeIzmx-zW
Excel Analysis Tutorial: https://youtube.com/playlist?list=PLhvn6aQRdimUhgO4q3gJWiz4QrbSVGmIz
...
https://www.youtube.com/watch?v=obFhBcdJvM4
Transaction
Created
1 month ago
Content Type
Language
video/mp4
English
Controlling
VIDEO
IMPOR
import-txt-data-to-csv-xlsx-text-to
Learn More:- https://exceltutorial.net
=============================================================
Previously In this Channel;
✔ Extract Unique Values With Advance Filter:- https://www.youtube.com/watch?v=ZW-0gIDLtGY
? Create Drop Down List In Excel:- https://www.youtube.com/watch?v=xhE4XZjbaAA
? Remove Blanks In Excel:- https://www.youtube.com/watch?v=gwSVVbgfKUo
? Alt Key Shortcuts in Excel:- https://www.youtube.com/watch?v=6OkKe-eIQ14
? CTRL G In Excel - https://www.youtube.com/watch?v=KU7ZNFWaqNc
?customize Quick Access Toolbar:- https://www.youtube.com/watch?v=ONC_dmV9XbI
?Create a Custom Tab:- https://www.youtube.com/watch?v=kN0gnlwEelM
? Excel Navigation Shortcuts:- https://www.youtube.com/watch?v=9Ugh-oLv2DA
?Customize Excel Footer Stats:- https://www.youtube.com/watch?v=c68VKmFx_6w
?Flash Fill in Excel: https://www.youtube.com/watch?v=dtfXkmalQyM
?Fix 6 Common Excel Errors You're Sick Of Seeing:- https://youtu.be/JjCC-4FKQCo
?Get Email name from Email Address:- https://www.youtube.com/watch?v=5HUH7SeBDLY
?Add Row Index In Power Query:- https://youtu.be/jbZTdpg7ZZc
?Month Name and Number :- https://www.youtube.com/watch?v=WJexkRPo-SY
?COUNTIF:- https://www.youtube.com/watch?v=gpYCgLqciuA
?Rounddown:- https://www.youtube.com/watch?v=U13Ounbk4Jg
?COUNT FUNCTIONS:- https://www.youtube.com/watch?v=NBPgxlFqg1E
============================================================
Import or convert TXT Data to CSV/Excel using Text to column in excel or you can even import .txt file data directly by opening into excel.
Watch this episode of excel tips where you can import raw data of txt file to column-wise data using text to column feature in view ribbon in excel.
It is indeed very important tool where you can easily integrate any file which is delimited with space, symbols, tab and convert it into your excel file.
...
https://www.youtube.com/watch?v=OUYUKv1HP9Y
Transaction
Created
1 month ago
Content Type
Language
video/mp4
English
Controlling
VIDEO
COUNT
count-characters-in-excel-excel-tips
Learn more about excel - https://exceltutorial.net
============================================================
Count and Sum Functions in Excel - https://youtube.com/playlist?list=PLhvn6aQRdimVHQA5zqrJ6XG6e5jP5hyQN
============================================================
Other Playlists:-
Learn Data Validation In Hindi: https://youtube.com/playlist?list=PLhvn6aQRdimXirM12gw1eijuiMYta-Oqf
Learn Excel Tutorials Online:- https://www.youtube.com/playlist?list=PLhvn6aQRdimWKL2jOmJxQBEBTeIzmx-zW
Excel Analysis Tutorial: https://youtube.com/playlist?list=PLhvn6aQRdimUhgO4q3gJWiz4QrbSVGmIz
Excel Formulas: https://www.youtube.com/playlist?list=PLhvn6aQRdimWPxkOxbRjkFCGcEp6-kMtJ
Excel Errors: https://www.youtube.com/playlist?list=PLhvn6aQRdimVXfu4qRN88TS7L1HgZ_rXI
Excel Power Query Masterclass:- https://youtube.com/playlist?list=PLhvn6aQRdimW7duwTzNlI5MJrwRZW7ki1
Printing In Excel:- https://youtube.com/playlist?list=PLhvn6aQRdimUXBeW973mOHAVKfT2-g3MO
============================================================
Practice Sheets: https://docs.google.com/spreadsheets/d/1sM7wcnSdKfwj27SOAO8CPIx9q_oBVXzwbsWdhhZowF8/edit?usp=sharing
============================================================
In this video of count and sum formulas, We will count the number of characters in a cell, the process is very similar to count specific word in excel, but using different functions like SUM, LEN, SUBSTITUTE AND LOWER Functions, Browse through the table of content below to learn more;
Table Of Content:
00:00 Introduction
00:56 Counting Character Length of Cell Using LEN Function
01:20 Adding Values of Character of All Cell Range Using SUM
02:48 Array Function
03:56 Counting Specific Character In Cell
05:28 Counting Specific Character in Cell Range
06:50 Conclusion
Hope you get to know what we are trying to say, if you want to learn more please do subscribe to our channel and do buy our Excel Masterclass Course.
Disclaimer:- The Character/person in the cover picture is for representation purposes only.
...
https://www.youtube.com/watch?v=y1kwBPoqYck
Transaction
Created
1 month ago
Content Type
Language
video/mp4
English
Controlling
VIDEO
GET E
get-email-name-from-email-address-left,
Learn Domain Name from email Address : https://www.exceltutorial.net/videos/30/email-name-from-email/
Learn more:- https://exceltutorial.net/
=============================================================
Previously In this Channel;
Add Row Index In Power Query:- https://youtu.be/jbZTdpg7ZZc
Month Name and Number :- https://www.youtube.com/watch?v=WJexkRPo-SY
COUNTIF:- https://www.youtube.com/watch?v=gpYCgLqciuA
Rounddown:- https://www.youtube.com/watch?v=U13Ounbk4Jg
COUNT FUNCTIONS:- https://www.youtube.com/watch?v=NBPgxlFqg1E
=============================================================
Email Name from Email Address
=============================================================
If you want to find out the name from an email address, you have walked into the right place?. To find the same, all you need is the LEFT Function and FIND Function.
Workflow : =LEFT(Cell number,FIND(“@”,Cell number)-1)
Follow this video for complete overview.
=============================================================
...
https://www.youtube.com/watch?v=5HUH7SeBDLY
Transaction
Created
1 month ago
Content Type
Language
video/mp4
English
Controlling
VIDEO
ADD R
add-row-index-in-power-query-add-index
Learn more:- https://exceltutorial.net/add-row-index-power-query/
=============================================================
Previously In this Channel;
Month Name and Number :- https://www.youtube.com/watch?v=WJexkRPo-SY
COUNTIF:- https://www.youtube.com/watch?v=gpYCgLqciuA
Rounddown:- https://www.youtube.com/watch?v=U13Ounbk4Jg
COUNT FUNCTIONS:- https://www.youtube.com/watch?v=NBPgxlFqg1E
...
https://www.youtube.com/watch?v=jbZTdpg7ZZc
Transaction
Created
1 month ago
Content Type
Language
video/mp4
English
Controlling
VIDEO
EXCEL
excel-power-query-limitations-excel-vs
Learn More:- https://exceltutorial.net
============================================================
Previously In Excel Power Query Masterclass :-
Episode 1 - Introduction To Power Query - https://www.youtube.com/watch?v=e24eqV5r5MQ
============================================================
Other Playlists:-
Learn Excel Tutorials Online:- https://www.youtube.com/playlist?list=PLhvn6aQRdimWKL2jOmJxQBEBTeIzmx-zW
Excel Analysis Tutorial: https://youtube.com/playlist?list=PLhvn6aQRdimUhgO4q3gJWiz4QrbSVGmIz
Excel Formulas: https://www.youtube.com/playlist?list=PLhvn6aQRdimWPxkOxbRjkFCGcEp6-kMtJ
Excel Errors: https://www.youtube.com/playlist?list=PLhvn6aQRdimVXfu4qRN88TS7L1HgZ_rXI
========================================================='===
In this episode of Power Query, We will be learning about Excel Power Query's limitations where we will explore two basic limitation which powerquery lags behind which tells you the difference between excel power query and excel itself.
Table Of Content:
00:00 Introduction
01:40 Limitation 1 - Changing Data (Example 1)
03:10 Limitation 2 - Blank Cells/Null/Error (Example 2)
07:40 Conclusion
Hope you get to know what we are trying to say, if you want to learn more please do subscribe to our channel and do buy our Excel Masterclass Course.
Disclaimer:- The Character/person in cover picture is for representation purpose only.
...
https://www.youtube.com/watch?v=SniGpSzCniM
Transaction
Created
1 month ago
Content Type
Language
video/mp4
English
Controlling
VIDEO
PROTE
protection-in-excel-protect-your
Learn More:- https://exceltutorial.net
============================================================
Excel Power Query Masterclass:- https://youtube.com/playlist?list=PLhvn6aQRdimW7duwTzNlI5MJrwRZW7ki1
============================================================
Other Playlists:-
Learn Excel Tutorials Online:- https://www.youtube.com/playlist?list=PLhvn6aQRdimWKL2jOmJxQBEBTeIzmx-zW
Excel Analysis Tutorial: https://youtube.com/playlist?list=PLhvn6aQRdimUhgO4q3gJWiz4QrbSVGmIz
Excel Formulas: https://www.youtube.com/playlist?list=PLhvn6aQRdimWPxkOxbRjkFCGcEp6-kMtJ
Excel Errors: https://www.youtube.com/playlist?list=PLhvn6aQRdimVXfu4qRN88TS7L1HgZ_rXI
========================================================='===
Practice Sheets: https://docs.google.com/spreadsheets/d/1sM7wcnSdKfwj27SOAO8CPIx9q_oBVXzwbsWdhhZowF8/edit?usp=sharing
============================================================
In this video, We will be learning how to protect your worksheet, workbook and lock cells from sharing or put password in your workbook and a lots more in single video. Do checkout table of contents below for the same;
Table Of Content:
00:00 Introduction
00:43 Insert Password In Excel
02:29 Protect Workbook in Excel
03:50 Protect Worksheet in Excel
07:10 Protect Cells in Excel
11:00 Password for Modification
11:20 Make Workbook Read-Only
14:50 Conclusion
Hope you get to know what we are trying to say, if you want to learn more please do subscribe to our channel and do buy our Excel Masterclass Course.
Disclaimer:- The Character/person in the cover picture is for representation purposes only.
...
https://www.youtube.com/watch?v=BYzUiTxQr9s
Transaction
Created
1 month ago
Content Type
Language
video/mp4
English
Controlling
VIDEO
2 EAS
2-easy-ways-to-sum-multiple-worksheets
Learn more about excel - https://exceltutorial.net
============================================================
In this video, I'm going to show you five different ways to copy or move a formula in Excel. Sometimes it can be hard to find the right way of copying or moving a formula, so I hope this video will help you out!
There are five ways to copy or move a formula in Excel!
The first is to use the Copy and Paste feature.
Next, you can use drag and drop. You need to click on the cell with the formula, then hold down your mouse button as you drag it over cells that should also have this formula. Click where you want it copied or moved to release your mouse button. Now press Ctrl+V for paste!
Another way is using a keyboard shortcut: Alt + Shift + C will copy a formula while Alt+Shift+X will move a formula from one spot to another on your spreadsheet. And finally, there's an option called "Fill Down" which copies formulas below other cells of equal value
Table Of Content:
00:00 Introduction
01:14 Copy Formula in Excel
03:19 Fill Formula In Rows or Columns
04:18 Absolute Reference
06:16 Move a Formula
08:20 Bonus Tip
09:38 Conclusion
============================================================
Cell References in Excel - https://youtube.com/playlist?list=PLhvn6aQRdimVC65nzr2BebSN9kPSdK6qH
============================================================
Other Playlists:-
Logical Functions in Excel :- https://youtube.com/playlist?list=PLhvn6aQRdimWBCKHNrLEqubXDMaHeINU1
Count and Sum Functions in Excel - https://youtube.com/playlist?list=PLhvn6aQRdimVHQA5zqrJ6XG6e5jP5hyQN
Learn Data Validation In Hindi: https://youtube.com/playlist?list=PLhvn6aQRdimXirM12gw1eijuiMYta-Oqf
Learn Excel Tutorials Online:- https://www.youtube.com/playlist?list=PLhvn6aQRdimWKL2jOmJxQBEBTeIzmx-zW
Excel Analysis Tutorial: https://youtube.com/playlist?list=PLhvn6aQRdimUhgO4q3gJWiz4QrbSVGmIz
Excel Formulas: https://www.youtube.com/playlist?list=PLhvn6aQRdimWPxkOxbRjkFCGcEp6-kMtJ
Excel Errors: https://www.youtube.com/playlist?list=PLhvn6aQRdimVXfu4qRN88TS7L1HgZ_rXI
Excel Power Query Masterclass:- https://youtube.com/playlist?list=PLhvn6aQRdimW7duwTzNlI5MJrwRZW7ki1
Printing In Excel:- https://youtube.com/playlist?list=PLhvn6aQRdimUXBeW973mOHAVKfT2-g3MO
============================================================
Practice Sheets: https://docs.google.com/spreadsheets/d/1sM7wcnSdKfwj27SOAO8CPIx9q_oBVXzwbsWdhhZowF8/edit?usp=sharing
============================================================
Hope you get to know what we are trying to say, if you want to learn more please do subscribe to our channel and do buy our Excel Masterclass Course.
Disclaimer:- The Character/person in the cover picture is for representation purposes only.
...
https://www.youtube.com/watch?v=4hbCv0CBLZc
Transaction
Created
1 month ago
Content Type
Language
video/mp4
English
Controlling
VIDEO
COUNT
count-specific-word-occurs-in-a-cell-how
Learn more about excel - https://exceltutorial.net
============================================================
Count and Sum Functions in Excel - https://youtube.com/playlist?list=PLhvn6aQRdimVHQA5zqrJ6XG6e5jP5hyQN
============================================================
Other Playlists:-
Learn Data Validation In Hindi: https://youtube.com/playlist?list=PLhvn6aQRdimXirM12gw1eijuiMYta-Oqf
Learn Excel Tutorials Online:- https://www.youtube.com/playlist?list=PLhvn6aQRdimWKL2jOmJxQBEBTeIzmx-zW
Excel Analysis Tutorial: https://youtube.com/playlist?list=PLhvn6aQRdimUhgO4q3gJWiz4QrbSVGmIz
Excel Formulas: https://www.youtube.com/playlist?list=PLhvn6aQRdimWPxkOxbRjkFCGcEp6-kMtJ
Excel Errors: https://www.youtube.com/playlist?list=PLhvn6aQRdimVXfu4qRN88TS7L1HgZ_rXI
Excel Power Query Masterclass:- https://youtube.com/playlist?list=PLhvn6aQRdimW7duwTzNlI5MJrwRZW7ki1
Printing In Excel:- https://youtube.com/playlist?list=PLhvn6aQRdimUXBeW973mOHAVKfT2-g3MO
============================================================
Practice Sheets: https://docs.google.com/spreadsheets/d/1sM7wcnSdKfwj27SOAO8CPIx9q_oBVXzwbsWdhhZowF8/edit?usp=sharing
============================================================
In this video, We will discuss how to count specific word occurs in a cell as we have done before but the case was to count in different cells. Heres the limitation of count functions. You can not count a specific word occurs in a cell, therefore we will use LEN and SUBSTITUTE Function to resolve the same. To continue learning, browse the content below;
Table Of Content:
00:00 Introduction
01:08 Adding Helper Value
02:22 Adding Length of the cell using LEN Function
03:10 Substituting Value with Blank Value using LEN & SUBSTITUTE Function
04:16 Calculating the number of John in Specific Cell
04:57 Complete formula to Count Specific Word Occurs in a Cell
07:32 Conclusion
Hope you get to know what we are trying to say, if you want to learn more please do subscribe to our channel and do buy our Excel Masterclass Course.
Disclaimer:- The Character/person in the cover picture is for representation purposes only.
...
https://www.youtube.com/watch?v=a6vizywaysQ
Transaction
Created
1 month ago
Content Type
Language
video/mp4
English