This tutorials covers how to generate series of dates increment by workdays from a single start date in Excel If need to generate a dynamic series of dates that are workdays only (i.e. Monday through Friday), you can do so with a simple formula that uses the the WORKDAY function Formula =WORKDAY(date,1) Explanation In the …
To calculate the total interest for a loan in a given year, you can use the CUMIPMT function. Formula =CUMIPMT(rate,nper,pv,start,end,type) Explanation In the example shown, the total interest paid in year 1 is calculated by using 1 for start period and 12 for end period. The The formula in F5 is: =CUMIPMT(5%/12,60,30000,1,12,0) Note: values hardcoded for readability only. …
To calculate the periodic interest rate for a loan, given the loan amount, the number of payment periods, and the payment amount, you can use the RATE function. Formula =RATE(periods,payment,-amount)*12 Explanation In the example shown, the formula in C10 is: =RATE(C7,C6,-C5)*12 Loans have four primary components: the amount, the interest rate, the number of periodic …
To calculate the interest portion of a loan payment in a given period, you can use the IPMT function. Formula =IPMT(rate,period,periods,-loan) Explanation In the example shown, the formula in C10 is: =IPMT(C6/12,1,C8,-C5) How this formula works For this example, we want to calculate the interest portion for payment 1 of a 5-year loan of $5,000 …
To calculate the cumulative principal paid between any two loan payments, you can use the CUMPRINC function. Formula =CUMPRINC(rate,nper,pv,start,end,type) Explanation In the example shown, we calculate the total principal paid over the full term of the loan by using the first and last period. The formula in C10 is: =CUMPRINC(C6/12,C8,C5,1,60,0) How this formula works For this example, …
To calculate the cumulative principal paid between any two loan payments, you can use the CUMIPMT function. In the example shown, we calculate the total principal paid over the full term of the loan by using the first and last period. Formula =CUMIPMT(rate,nper,pv,start,end,type) Explanation The formula in C10 is: =CUMIPMT(C6/12,C8,C5,1,60,0) How this formula works For this example, …
To calculate compound interest in Excel, you can use the FV function. Case study: This example assumes that $1000 is invested for 10 years at an annual interest rate of 5%, compounded monthly. Formula =FV(rate,nper,pmt,pv) Explanation In the example shown, the formula in C10 is: =FV(C6/C8,C7*C8,0,-C5) How this formula works The FV function can calculate compound …
To calculate Compound Annual Growth Rate (CAGR) in Excel, the average rate of return for an investment over a period of time, you can use several approaches. Formula =(end/start)^(1/periods)-1 Note that unlike most other financial functions in Excel, future value (FV) does not need to be entered as a negative number. Explanation In the example …
This tutorial show how to calculate the value of a bond on the issue date using the PV function. Explanation In the example shown, the formula in C10 is: =-PV(C6/C8,C7*C8,C5/C8*C4,C4) Note: This example assumes that today is the issue date, so the next payment will occur in exactly six months. See note below on finding the …
An annuity is a series of equal cash flows, spaced equally in time. To solve for an annuity interest rate, you can use the RATE function. Case study: The goal in this example is to have $100,000 at the end of 10 years, with an annual payment of $7,500 made at the end of each year. …
To calculate annual compound interest, you can use a formula based on the starting balance and annual interest rate. Formula =start+(start*rate) Explanation In the example shown, the formula in C6 is: =C5+(C5*rate) Note: “rate” is the named range F6. How this formula works If you have an annual interest rate, and a starting balance you …
To get the present value of an annuity, you can use the PV function. Formula =PV(rate,periods,payment,0,0) In the example shown, the formula in C9 is: =PV(C5,C6,C4,0,0) Explanation An annuity is a series of equal cash flows, spaced equally in time. In this example, an annuity pays 10,000 per year for the next 25 years, with …
This tutorial shows how to solve for an annuity payment in Excel. An annuity is a series of equal cash flows, spaced equally in time. Case study: Using the PMT function, the goal in this example is to have 100,000 at the end of 10 years, with an interest rate of 5%. Payments are made annually, …
NPV formula for net present value To calculate Net Present Value (NPV) you can use the NPV function. Formula =NPV(rate,cashflows)-initialcost Explanation In the example shown, the formula in F6 is: =NPV(F4,C6:C10)+C5 How this formula works Net Present Value (NPV) is the present value of expected future cash flows minus the initial cost of investment. The …
This tutorial shows how present value and future value are related using the example below: Explanation In the example shown, Years, Compounding periods, and Interest rate are linked in columns C and F like this: F5=C9 F6=C6 F7=C7 F8=C8 The formula to calculate future value in C9 is: =FV(C8/C7,C6*C7,0,-C5,0) The formula to calculate present value in F9 …
To get the present value of an annuity, you can use the PV function. Formula =FV(rate,periods,payment) Explanation In the example shown, the formula in C7 is: =FV(C5,C6,-C4,0,0) Explanation An annuity is a series of equal cash flows, spaced equally in time. In this example, a $5000 payment is made each year for 25 years, with …
To calculate simple interest in Excel (i.e. interest that is not compounded), you can use a formula that multiples principal, rate, and term. Case Study: This example assumes that $1000 is invested for 10 years at an annual interest rate of 5%. Simple interest means that interest payments are not compounded – the interest is …
To calculate the principal portion of a loan payment in a given period, you can use the PPMT function. Formula =PPMT(rate,period,periods,-loan) Explanation In the example shown, the formula in C10 is: =PPMT(C6/12,1,C8,-C5) How this formula works For this example, we want to calculate the principal portion for payment 1 of a 5-year loan of $5,000 …
To calculate the number of periods needed for an annuity to reach a given future value, you can use the NPER function. Formula =NPER(rate,pmt,pv,fv,type) Explanation In the example shown C9 contains this formula: =NPER(C6,-C7,-C4,C5,0) Explanation An annuity is a series of equal cash flows, spaced equally in time. The goal in this example is to …
To calculate the number of payment periods for a loan, given the loan amount, the interest rate, and a periodic payment amount, you can use the NPER function. Formula =NPER(rate,payment,-loan) Explanation In the example shown, the formula in C10 is… =NPER(C6/12,C7,-C5) How this formula works Loans have four primary components: the amount, the interest rate, …
To calculate a loan payment amount, given an interest rate, the loan term, and the loan amount, you can use the PMT function. Formula =PMT(rate,periods,-amount) Explanation In the example shown, the formula in C10 is: =PMT(C6/12,C7,-C5) How this formula works Loans have four primary components: the amount, the interest rate, the number of periodic payments …
To calculate the original loan amount, given the loan term, the interest rate, and a periodic payment amount, you can use the PV function. Formula =PV(rate,periods,-payment) Explanation In the example shown, the formula in C10 is… =PV(C5/12,C7,C6) How this formula works Loans have four primary components: the amount, the interest rate, the number of periodic …
To test a cell for one of many strings, while excluding others, you can use a formula based on the SEARCH, ISNUMBER, and SUMPRODUCT functions. Formula =(SUMPRODUCT(–ISNUMBER(SEARCH(include,A1)))>0) *(SUMPRODUCT(–ISNUMBER(SEARCH(exclude,A1)))=0) Note: this formula returns either 1 or zero, which are handled like TRUE and FALSE in formulas, conditional formatting, or data validation. Explanation In the example shown the …
To check if a cell contains specific text, you can use the SEARCH function together with the ISNUMBER function. In the generic version, substring is the specific text you are looking for, and text represents text in the cell you are testing. Formula =ISNUMBER(SEARCH(substring,text)) Explanation In the example shown, the formula in D5 is: =ISNUMBER(SEARCH(C5,B5)) This formula returns TRUE if …
To test if a worksheet name exists in a workbook, you can use a formula based on the ISREF and INDIRECT functions. Formula =ISREF(INDIRECT(“sheetname”&”!A1″)) Explanation In the example shown, the formula in C5 is: =ISREF(INDIRECT(B5&”!A1″)) How this formula works The ISREF function returns TRUE for a valid worksheet reference and FALSE is not. In this …
If you want to get the workbook name only (i.e. the file name without path or sheet name) you can do so with a rather long formula that uses the MID function along with the FIND function. Formula =MID(CELL(“filename”,A1),FIND(“[“,CELL (“filename”,A1))+1,FIND(“]”,CELL(“filename”,A1)) -FIND(“[“,CELL(“filename”,A1))-1) Explanation How the formula works The cell function is used to get the full …
If you want to get the current workbook’s full name and path without a sheet name, you can use a formula that employs several text functions to strip off the sheet name. The final result will be a text string that looks like this: path[workbook.xlsm] Formula =SUBSTITUTE( LEFT(CELL(“filename”,A1),FIND(“]”,CELL(“filename”,A1))-1),”[“,””) Explanation How the formula works The CELL …
If you want to get the sheet name only (i.e. the sheet name without the file name or path) you can do so with rather long formula that uses the MID function along with the FIND function. The final result will look something like this: Sheet1 Formula =MID(CELL(“filename”,A1),FIND(“]”, CELL(“filename”,A1))+1,255) Explanation How the formula works The …
If you want to get the current workbook’s full path, name, and sheet with a formula, you can use the CELL function and a reference to any cell in the workbook. CELL will return the name in this format: path[workbook.xlsx]sheetname Note that you must save the worksheet in order to get the a result. Formula …
This tutorials shows how to Get decimal part of a number in Excel. If you need to get just de the decimal part of a number, you can use a simple formula based on the TRUNC function. Formula =number-TRUNC(number) Explanation In the example, cell C6 contains this formula: =B6-TRUNC(B6) This formula uses the TRUNC function …
This tutorials shows how to Get integer part of a number in Excel. To remove the decimal part of a number and return only the integer portion, you can use the TRUNC function to slice off the decimal. Formula =TRUNC(number) Explanation In the example, cell C6 contains this formula: =TRUNC(B6) The TRUNC function simply truncates …
This tutorials shows how to get the number at a specific place value in Excel. To get the number at a specific place value you can use a formula based on the MOD function. By place value, we mean hundred thousands, ten thousands, thousands, hundreds, tens, ones, etc. Formula =MOD(number,place*10) – MOD(number,place) Explanation In the …
If you need to round a number in a normal fashion (i.e. round values of 5 or more up and values less than 5 down) you can use the ROUND function with a specified number of digits. This tutorials shows how to Round specific number in Excel. Formula =ROUND(number,digits) Explanation In the example, the formula in …
If you need to round to force any number to round down, regardless of its value you can use the ROUNDDOWN function with a specified number of digits. This tutorials shows how to Round a number down in Excel. Formula =ROUNDDOWN(number,digits) Explanation In the example, the formula in cell D7 is =ROUNDDOWN(B7,C7) This tells Excel …
This tutorials shows how to Round time to nearest 15 minutes in Excel. To round a time to the nearest 15 minute interval, you can use the MROUND function, which rounds based on a supplied multiple. Formula =MROUND(time,”0:15″) Explanation In the example shown, the formula in C6 is: =MROUND(B6,”0:15″) How this formula works MROUND rounds to …
This tutorials shows how to Round to nearest 1000 in Excel If you need to round a number to the nearest 1000, you can use the ROUND function and supply -3 for number of digits. Formula =ROUND(number,-3) Explanation In the example, cell C6 contains this formula: =ROUND(B6,-3) The value in B6 is 1,234,567 and the …
This tutorials shows how to Round to nearest 5 in Excel. If you need to round a number to the nearest multiple of 5, you can use the MROUND function and supply 5 for number of digits. Formula =MROUND(number,5) Explanation In the example, cell C6 contains this formula: =MROUND(B6,5) The value in B6 is 17 …
This tutorials shows how to Round a number down to nearest multiple in Excel. If you need to round a number down to the nearest specified multiple (i.e. round a number down to the nearest dollar, down to the nearest $.25, down to the nearest multiple of 5, etc.) you can use the FLOOR function. Formula =FLOOR(number,multiple) …
If you want to test a cell to see if it equals one of several things, you can do so with a formula that uses the SUMPRODUCT function. Case study: Let’s say you have a list of text strings in the range B5:B11, and you want to test each cell against another list of things …
This tutorials shows how to rank items in a list using one or more criteria in Excel. To achieve this you can use the COUNTIFS function. Formula =COUNTIFS(criteria_range,criteria,values,”>”&value)+1 Explanation In the example shown, the formula in E5 is: =COUNTIFS(groups,C5,scores,”>”&D5)+1 where “groups” is the named range C5:C14, and “scores” is the named range D5:D14. The result is a …