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.
Download Excel Examples!
JOIN OUR FREE NEWSLETTER AND GET
report "Top 7 IFRS Mistakes" + free IFRS mini-course
Please check your inbox to confirm your subscription.
- Silvia on 3 Biggest Myths in Accounting for PPE
- Silvia on IAS 16 Property, Plant and Equipment – summary
- Silvia on Hedge Accounting: IAS 39 vs. IFRS 9
- Ana Fuenmayor on Hedge Accounting: IAS 39 vs. IFRS 9
- Md. Omar on IAS 16 Property, Plant and Equipment – summary
- Accounting Policies and Estimates (12)
- Consolidation and Groups (24)
- Current Assets (21)
- Financial Instruments (54)
- Financial Statements (45)
- Foreign Currency (9)
- IFRS Videos (63)
- Insurance (1)
- Most popular (6)
- Non-current Assets (54)
- Other Topics (15)
- Provisions and Other Liabilities (43)
- Revenue Recognition (25)
Thanks Silvia for your worthy contribution.
Just one question as to why IAS-16 use the words depreciation and amortisation, interchangeably. A common understanding is that the word amortisation is used for intangible assets.
Thanks a million.
Hi Silvia, Regarding IRR calculation, if a bond purchase price includes accrued interest at the time of bond purchase as well as premium on face value, should we calculate effective rate based on actual purchase price (and including full coupon payment on first coupon date) or by excluding accrued interest from the price (and excluding accrued interest from total c0upon payment on first coupon date)?
Further, At initial recognition, is the bond recognized at purchase price including accrued interest or without including accrued interest?
You are doing a great working by helping the students with your knowledge. I really appreciate your work on IFRS, I also liked your videos on youtube. Your explanation about IFRS is very good and easy to understand.
I have been using Microsoft Excel for donkey years, yet i don’t even know such great formula. 🙂
Thank you, Silvia.
you are great 🙂
Thank you Silvia. Your IFRS teaching helping me to further strengthen my knowledge.
Your article is brilliant, in fact all articles I’ve read are really concise and easy to understand. However I’m struggling in understanding the difference between the IRR and EIR. Sorry if this seems a silly question.
Ho Aaliyah, it’s not a silly question, because in fact, there’s not much difference between these 2 rates.
IRR = internal rate of return = it’s the rate that if you apply it to your cash flows and discount them to present value, total net present value is zero.
EIR = effective interest rate = in fact, this is the internal rate of return of the cash flows from your loan, lease or whatever you have. Just the definition is set in a different way, because it says that EIR is the rate that exactly discounts estimated future cash payments or receipts through the expected life of the financial instrument to the net carrying amount of the financial asset or liability. In other words – it is the internal rate of return of all future cash payments and receipts plus net carrying amount in the present time.
Hope it helps.
Thank you, Silvia
Extremely useful info!
I had really hard time presenting the auditors the NPV’s and IRR’s of some debt portfolios
You made it VERY clear.
You are a genius!! Thanks for the excel file and the explanations.
Thank you, Yas 🙂
ur summary presentation on the ias 1, & 16 has been really helpful. could it be possible to have one on 8, and others.
Yes, qwame, I am adding new summaries all the time, just subscribe and you’ll get info about new vids. It takes time so please be patient. And thank you for the comment!
Thanx for excel, it really helps!