calculating weighted average cost of capital in a macro
 

I'm trying to write a macro to calculate the weighted average cost of
capital, based on values inputted by the user who executes the macro.
Following is the code I have but it occurs to me that this looks
rather ungainly. Is there a better way to write code of this sort:

Sub CalculateWACC()
Dim MyString1 As String, MyString2 As String
Dim MyString3 As String, MyString4 As String
Dim MyString5 As String
'Prompts the user for values to input to calculate the weighted
average
'cost of capital, based on the formula
'c = (E/K) * y + (D/K) * b(1 - t)
'whe K = D + E
'c = weighted average cost of capital (%)
'y = required or expected return on equity (%)
'b = required or expected return on borrowings (%)
't = corporate tax rate (%)
'D = total debt and leases (currency)
'E = total equity and equity equivalents (currency)
'K = total capital invested in the going concern (currency)
MyString1 = Application.InputBox("enter required or expected
return on equity")
MyString2 = Application.InputBox("enter required or expected
return on debt")
MyString3 = Application.InputBox("enter corporate tax rate")
MyString4 = Application.InputBox("enter total debt and leases")
MyString5 = Application.InputBox("enter total equity and equity
equivalents")
Worksheets("Analysis").Range("A1") = MyString1
Worksheets("Analysis").Range("A2") = MyString2
Worksheets("Analysis").Range("A3") = MyString3
Worksheets("Analysis").Range("A4") = MyString4
Worksheets("Analysis").Range("A5") = MyString5
Worksheets("Analysis").Range("A6") = "SUM(A4+A5)"
End Sub

Thanks

calculating weighted average cost of capital in a macro
 

Use a userform to get all the data at once. Here is some info:

http://j-walk.com/ss/excel/tips/tip84.htm

http://www.contextures.on.ca/xlUserForm01.html


See this tutorial here
http://www.dicks-blog.com/excel/2004...g_userfor.html

http://support.microsoft.com/default...b;en-us;829070
How to use Visual Basic for Applications examples to control UserForms in
Microsoft Excel


XL97: How to Use a UserForm for Entering Data (Q161514)
http://support.microsoft.com/?id=161514

XL2000: How to Use a UserForm for Entering Data (Q213749)
http://support.microsoft.com/?id=213749


http://support.microsoft.com/kb/213574/en-us
XL2000: How to Display a UserForm Whose Name Is in a Variable

http://support.microsoft.com/kb/207714/en-us
XL2000: Run-Time Errors Using UserForms Collection


Here are some other sources of information:

http://www.microsoft.com/ExcelDev/Articles/sxs11pt1.htm
Lesson 11: Creating a Custom Form
Excerpted from Microsoft® Excel 97 Visual Basic® Step by Step.


http://support.microsoft.com/?id=168067
File Title: Microsoft(R) Visual Basic(R) for Applications Examples for
Controlling UserForms in Microsoft Excel 97
File Name: WE1163.EXE
File Size: 161742 bytes
File Date: 05/08/97
Keywords: kbfile
Description: This Application Note is an introduction to manipulating
UserForms in Microsoft Excel 97. It includes examples and Microsoft Visual
Basic for Applications macros that show you how to take advantage of the
capabilities of UserForms and use each of the ActiveX controls that are
available for UserForms

--
Regards,
Tom Ogilvy


"Dave F" wrote:

I'm trying to write a macro to calculate the weighted average cost of
capital, based on values inputted by the user who executes the macro.
Following is the code I have but it occurs to me that this looks
rather ungainly. Is there a better way to write code of this sort:

Sub CalculateWACC()
Dim MyString1 As String, MyString2 As String
Dim MyString3 As String, MyString4 As String
Dim MyString5 As String
'Prompts the user for values to input to calculate the weighted
average
'cost of capital, based on the formula
'c = (E/K) * y + (D/K) * b(1 - t)
'whe K = D + E
'c = weighted average cost of capital (%)
'y = required or expected return on equity (%)
'b = required or expected return on borrowings (%)
't = corporate tax rate (%)
'D = total debt and leases (currency)
'E = total equity and equity equivalents (currency)
'K = total capital invested in the going concern (currency)
MyString1 = Application.InputBox("enter required or expected
return on equity")
MyString2 = Application.InputBox("enter required or expected
return on debt")
MyString3 = Application.InputBox("enter corporate tax rate")
MyString4 = Application.InputBox("enter total debt and leases")
MyString5 = Application.InputBox("enter total equity and equity
equivalents")
Worksheets("Analysis").Range("A1") = MyString1
Worksheets("Analysis").Range("A2") = MyString2
Worksheets("Analysis").Range("A3") = MyString3
Worksheets("Analysis").Range("A4") = MyString4
Worksheets("Analysis").Range("A5") = MyString5
Worksheets("Analysis").Range("A6") = "SUM(A4+A5)"
End Sub

Thanks

calculating weighted average cost of capital in a macro
 

Thanks, I think this is the sort of info I was looking for.

Dave

On Aug 6, 3:02 pm, Tom Ogilvy
wrote:
Use a userform to get all the data at once. Here is some info:

http://j-walk.com/ss/excel/tips/tip84.htm

http://www.contextures.on.ca/xlUserForm01.html

See this tutorial herehttp://www.dicks-blog.com/excel/2004/09/linking_userfor.html

http://support.microsoft.com/default...b;en-us;829070
How to use Visual Basic for Applications examples to control UserForms in
Microsoft Excel

XL97: How to Use a UserForm for Entering Data (Q161514)http://support.microsoft.com/?id=161514

XL2000: How to Use a UserForm for Entering Data (Q213749)http://support.microsoft.com/?id=213749

http://support.microsoft.com/kb/213574/en-us
XL2000: How to Display a UserForm Whose Name Is in a Variable

http://support.microsoft.com/kb/207714/en-us
XL2000: Run-Time Errors Using UserForms Collection

Here are some other sources of information:

http://www.microsoft.com/ExcelDev/Articles/sxs11pt1.htm
Lesson 11: Creating a Custom Form
Excerpted from Microsoft® Excel 97 Visual Basic® Step by Step.

http://support.microsoft.com/?id=168067
File Title: Microsoft(R) Visual Basic(R) for Applications Examples for
Controlling UserForms in Microsoft Excel 97
File Name: WE1163.EXE
File Size: 161742 bytes
File Date: 05/08/97
Keywords: kbfile
Description: This Application Note is an introduction to manipulating
UserForms in Microsoft Excel 97. It includes examples and Microsoft Visual
Basic for Applications macros that show you how to take advantage of the
capabilities of UserForms and use each of the ActiveX controls that are
available for UserForms

--
Regards,
Tom Ogilvy



"Dave F" wrote:
I'm trying to write a macro to calculate the weighted average cost of
capital, based on values inputted by the user who executes the macro.
Following is the code I have but it occurs to me that this looks
rather ungainly. Is there a better way to write code of this sort:

Sub CalculateWACC()
Dim MyString1 As String, MyString2 As String
Dim MyString3 As String, MyString4 As String
Dim MyString5 As String
'Prompts the user for values to input to calculate the weighted
average
'cost of capital, based on the formula
'c = (E/K) * y + (D/K) * b(1 - t)
'whe K = D + E
'c = weighted average cost of capital (%)
'y = required or expected return on equity (%)
'b = required or expected return on borrowings (%)
't = corporate tax rate (%)
'D = total debt and leases (currency)
'E = total equity and equity equivalents (currency)
'K = total capital invested in the going concern (currency)
MyString1 = Application.InputBox("enter required or expected
return on equity")
MyString2 = Application.InputBox("enter required or expected
return on debt")
MyString3 = Application.InputBox("enter corporate tax rate")
MyString4 = Application.InputBox("enter total debt and leases")
MyString5 = Application.InputBox("enter total equity and equity
equivalents")
Worksheets("Analysis").Range("A1") = MyString1
Worksheets("Analysis").Range("A2") = MyString2
Worksheets("Analysis").Range("A3") = MyString3
Worksheets("Analysis").Range("A4") = MyString4
Worksheets("Analysis").Range("A5") = MyString5
Worksheets("Analysis").Range("A6") = "SUM(A4+A5)"
End Sub

Thanks- Hide quoted text -

- Show quoted text -