How to Calculate Pay Back Period in Excel

We have covered Pay Back Period in great detail. The process is not difficult and usually takes hardly 05 minutes. But, there are friends who want to know the exact years with decimal values. For them, we are sharing the Excel formula which you can use to find out PBP. You just need to enter initial investment amount and the remaining cash flows and our formula will do the rest of the work.

Example

ABC is considering investing in a capital budget which requires initial investment of $ 400,000. The cash flows are as follows:

Year Amount
0 400,000
1 125,000
2 200,000
3 145,000
4 150,000

 

We have developed the following Excel sheet:

 

From the above sheet, we can easily tell that project will recover its initial investment in the 3rd year. However, in order to calculate exact figure with decimals, we will usre this

Excel formula:

=(SUM($B$1:B4)>0)*(C3=0)*(ROW()-2-SUM($B$1:B3)/B4)

You can see in the above screen shot that initial investment of $ 400,000 is getting repaid in 2.51724 years. 

Contents
Financial Ratios Accounting Cycle Accounting Principles Financial Accounting Basics Financial Statements Reporting Bad Debts Current Assets Long-term Assets Voucher System Partnership Depreciation Work Sheet Difference Between Reserve and Fund Accounting for Leases Capital reduction and reconstruction Absorption of Company Amalgamation Accounting for Installment Sales Basis of Recording Profit and Loss Branch Accounting Construction Contracts Revenue Recognition Accounting for Groups Financial Analysis Events After the Balance Sheet Date Deferred Tax Cost Accounting Activity Based Costing (ABC) Throughput Accounting Relevant Cost Break Even Analysis Standard Costing Inventory Management Payroll Accounting Royalty Accounting Statistics Master Budget Salary Income Microsoft Excel Tutorial How to Count Number of Records between Two Dates How to sum values in excel based on a criteria Finding the first login and last logout time of employees How to Print Salary Slips Using VLookup Formula in Excel How to Calculate Net Present Value (NPV) in Excel Income tax calculation excel sheet for salaried individuals How to Calculate Pay Back Period in Excel How to Calculate Declining Balance in Excel Using Formula How to Calculate Double Declining Balance in Excel How to Calculate Number of Payments on A Loan In Excel Using NPER Count the Number of Excel Records That Fall Between Two Dates How to Count Cells between Two Numbers in Excel How to Prepare Exam Result in Excel 3D Sum Multiple Worksheets How to Count Specific Text in Excel Other Topics Share or Stock Valuation Model Financial Management Topics