Thread: Excel Ln and Exp function-Urgent help

Hi all,

please can someone explain this equation to me and what it trying to achieve. A little background- a client had a staff member who left who was valuing a financial instrument and used this equation in excel, but i don't know what it means and if it is actually giving the correct/ approximate value of this instrument. The instrument is a equity note based on the index of a bundle of shares

Here is the excel formula used

=(index value of bundle)*EXP((LN(1+interest rate)-dividend yield)*((expirery date of instrument-valuation date)/365))*number of instruments,

i don't know what LN and EXP do/are except for the fact that they are inverse to each other.

Please explain in Ley terms if possible as my business maths is non-existant

Thanks a lot,
Stu

Natural logarithm - Wikipedia, the free encyclopedia

http://en.wikipedia.org/wiki/E_(mathematical_constant)

Try those...

Originally Posted by stuarth

Hi all,

please can someone explain this equation to me and what it trying to achieve. A little background- a client had a staff member who left who was valuing a financial instrument and used this equation in excel, but i don't know what it means and if it is actually giving the correct/ approximate value of this instrument. The instrument is a equity note based on the index of a bundle of shares

Here is the excel formula used

=(index value of bundle)*EXP((LN(1+interest rate)-dividend yield)*((expirery date of instrument-valuation date)/365))*number of instruments,

i don't know what LN and EXP do/are except for the fact that they are inverse to each other.

Please explain in Ley terms if possible as my business maths is non-existant

Thanks a lot,
Stu

You should not be doing this as your level of understanding and expertise is not adequate to the task. Your employer should go to a competent professional for this work.

Also, your client should have had in place procedures for the design and documentation of the development of tools such as this. Then such a problem would not arrise as the supporting documention would exist.

Are your client and employer unaware of the risks of sloppy spreadsheet modelling technique?

CB

Thanks but actually i have valued this instrument using another model and my value approximates theirs, and yes i'm actually fully competent to be valuing such instruments, so shot for the feedback mate , my client asked me to help on these valuations and there was one done by a previous employee using this LN model, yes unfortunately this employee did not leave sufficient documentation for me/the new employee to understand this model. i would just like to gain an understanding as to what his model achieves as although my model approximates his values, it is not exact, the client has adopted my model going forward but if i can gain an understanding of what this LN model achieves and how it works i can either adjust my model appropriatly if there is something i have overlooked or help the client with the adjustment of their model.

Sorry i'm not meaning to be rude but i'm not looking for commentary on professional competence, if someone could help explain what this function in excel does and what it is achieving it would be greatly appreciated.
kind regards,

Originally Posted by stuarth

Thanks but actually i have valued this instrument using another model and my value approximates theirs, and yes i'm actually fully competent to be valuing such instruments,

You are not competent to be maintaining this spreadsheet model by you own words. Perhaps you should do some research on the cost to business of errors in spreadsheets and how most of these are due to lack of proper design and control of the program.

CB