Top Excel Formulas for IFRS
This is another useful tip I would like to give you. With these formulas, you can really speed up your IFRS accounting process, especially when dealing with sophisticated calculations. So let’s start.
Top Financial Formula #1: IRR
What is IRR?
IRR formula calculates Internal Rate of Return for series of cash flows. The cash flows must be expressed as numbers or values. And, they must occur in regular intervals—daily, weekly, monthly, annually, whatever you need.
What is internal rate of return? Simply speaking, it is an interest that you pay on initial loan (which is a negative value) with regular installments in later periods (which are positive values). Installments do not need to be the same in size, though.
Where do you need to apply IRR?
Certainly, if you need to perform valuation of financial assets or liabilities in line with IAS 39/IFRS 9, you need to calculate effective interest rate—that’s exactly internal rate of return for cash flows from your financial instrument.
Also, lease accounting in line with IAS 17 requires actuarial method to be applied. Sure you have to
use IRR there. If you wish to read more about internal rate of return, please refer to our article How to calculate interest rate implicit in the lease.
Top Financial Formula #2: NPV
What is NPV?
NPV calculates Net Present Value of an investment by using predetermined discount rate and series of cash flows: investment, future payments (negative values) and future receipts (positive values).
Net Present Value is a difference between the present value (or discounted) cash inflows and the present value (discounted) cash outflows of certain project. So, again put more simply, it is your return on investment expressed in today’s money.
Where do you apply NPV?
Net Present Value is a great formula for computation of potential profit or loss from the project and thus is used very widely for project appraisals.
In IFRS, NPV is used broadly in value in use calculation according to IAS 36. As you might know, value in use is critical for determining asset’s impairment loss.
Also, NPV is sometimes used in calculation of amount of provision according to IAS 37. When you have some onerous long-term contract, or some long-term restructuring, then NPV of cash flows from that restructuring or contract can well serve as the amount of your provision.
Top Financial Formula #3: YIELD
YIELD calculates on a security that pays periodic interest—for example, bonds or treasury bills. If an investor wants to know his real rate of return on some bond, then he usually looks for yield.
Again a little simplification: yield is internal rate of return of a series of cash flows from the security — initial purchasing price, periodic coupon or interest payments and redemption price (how much investor gets from the issuer of bond at its maturity).
Where do you apply YIELD?
YIELD formula widely applies mainly for valuation of fixed term securities in line with IAS 39 / IFRS 9. There are so many securities not publicly traded on the stock exchange and their fair value must be estimated by extrapolation along yield curve.
Also, when you are trying to determine appropriate discount rate for valuation of liabilities from employee benefits in line with IAS 19, then it is quite handy to know how to apply YIELD formula and extrapolate along yield curve.
Download Excel Examples
OK, the last formula was a bit difficult, but otherwise, if you learn to apply these 3, your IFRS accounting skills will certainly improve. For your convenience, I am enclosing Excel file with illustrative short examples of all 3 formulas above.
JOIN OUR FREE NEWSLETTER AND GET
report "Top 7 IFRS Mistakes" + free IFRS mini-course
Please check your inbox to confirm your subscription.
- Accounting Policies and Estimates (14)
- Consolidation and Groups (24)
- Current Assets (21)
- Financial Instruments (54)
- Financial Statements (48)
- Foreign Currency (9)
- IFRS Videos (63)
- Insurance (1)
- Most popular (6)
- Non-current Assets (54)
- Other Topics (15)
- Provisions and Other Liabilities (44)
- Revenue Recognition (26)